hi guys,
I have a data set (as shown in the attached sample file) in which I am trying to return the highest value or max value from column 2 based on a lookup value in column 1. The values in column 1 will have duplicates as their corresponding values in column 2 will be different.
I hope the sample file will explain the situation and the goal better if the above statement isn't clear.
Any help is appreciated
Thanks
VJ
is your version of excel - still 2010 ?
If so then
=MAX(IF(A2:A13=H3,B2:B13))
if later version then
=MAXIFS(B2:B13,A2:A13,H3)
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hello Wayne,
Appreciate the help. the formula worked!
I got a follow up question though. My version of excel is 2016 and I see that in the Account tab. But for some reason, I couldn't use your latter formula. I have had similar issues using XLOOKUP as well. Do you know why this is happening?
Thanks
VJ
its not in the standalone 2016 version as far as i know https://www.excelforum.com/excel-gen...available.html
please update profile to show 2016 version
Okay. Thanks for your help. Have a great day !
you are welcome, thanks for updating, makes it easier in providing a solution for your vesion
Hi Wayne,
An update after using the formula. It doesn't calculate if I change the values in cell H3. I tried it for my actual file and It is returning "0". I've re-attached the sample file after making changes to make it look similar to my actual data.
The formula only works if the Part numbers are in the same order as the source table.
thanks
VJ
you don't need the @ in front of the range
https://support.microsoft.com/en-us/...e-c1c999be2b34
Also its an Array formula - so needs to be
entered with Control + Shift + Enter
and get the {} brackets
my bad. I didn't use it as an Array formula. It works fine now.
thank you so much for the help. I truly appreciate it
VJ
you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks