Hi,
can anyone please advise what function to use to find maximum value from the column BA corresponding to the range of values in the column AZ (50 - 150) related to the selection from drop down menu in the column AV (100)?
For any value X selected in drop down menu, values in the column AZ will be ranging from X-50 to X+50. The corresponding maximum from column BA is required.
Thanks
Capture1.PNG
Last edited by wlade122; 11-06-2016 at 05:28 PM.
Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.
If you need to post an image post it in the *.jpg format.
Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Try
=VLOOKUP($AV$10,$AZ$1:$BA$20,2,0)
???
This one unfortunately only finds value corresponding to the selection. For the given range of values from 100-50=50 to 100+50=150 the maximum should be 0.8
Try
=MAX(IF(($AZ$8:$AZ$49>=AV20-50)*($AZ$8:$AZ$49<=AV20+50),$BA$8:$BA$49))
Enter with Ctrl+Shift+Enter
(Mis-read your post!)
Many thanks, it works perfect.
What is the significance of Ctrl+Shift+Enter in general?
It is used in "array formula"
https://support.office.com/en-gb/art...2-ecfd5caa57c7
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks