Reference Sheet
A | B
5 | Frog
Data Sheet
A | B | C
4 8 Dog
5 4 Cat
3 1 Chicken
5 6 Frog
On the Reference Sheet in column B i want to have a formula that would look up the value in Reference Sheet column A, in Data Sheet column A
There will be multiple matches, but it needs to find the one with the largest number in Data Sheet column B, and then return the value in Data Sheet column C
I've been trying to do this for 2 days! lol
Thanks
If you sort Column B on your Data Sheet from largest to smallest, your VLOOKUP formula will result the one with the largest number. Would that work for you?
You get better help on your question if you add a small excel file, without confidential information.
Please also add manualy the expected result in your file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
For demonstration purposes I've put everything on a single sheet. You'll need to use the appropriate sheet names for your application.
Data Range
This array formula** entered in B2:
=INDEX(F2:F5,MATCH(MAX(IF(D2:D5=A2,E2:E5)),IF(D2:D5=A2,E2:E5),0))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks