Returning Cell Value if Matches Another cell
 

I know I've posted this before, but I can't seem to get it to work. I've tried VLOOKUP, MATCH, LOOKUP, and such with inconsistent results. Here's what I'm trying to do:

I have Percentage Values in Column A and Numbers corresponding to them in Column B - I want to return the three largest values from Column A in Column D with the corresponding Numbers from Column B in Column E. Such as:

Column A Column B Column C Column D Column E
2.3565 1 BLANK 2.66354 5
2.4569 2 BLANK 2.5369 3
2.5369 3 BLANK 2.4569 2
2.11547 4 BLANK
2.66354 5 BLANK

And if there happen to be two or more percentage values with the same value, to return them as well individually.
Any/all assistance would be greatly appreciated.

Thank you in advance for your help,
Frank

Is there any reason you can't just sort the sheet descending by column A?

If you can't, this would be easiest in VB.


However if you need to do this with formulas, for column D you could potentially use MAX combined with other formulas. D1 is an easy win (MAX). D2 you would need to find the cell reference in column A that matches D1, then get a MAX for above and below that cell. Same concept for D3, just lots more matching and maxing.

Then in column E use something like this:

=INDEX(B:B,MATCH(D1,A:A))


Hope that helps.

Thank you for the reply. Can you walk me through the steps with examples, please? I am having a hard time seeing how this is done.

By the way, I don't how to do this in VB. If you can show me the steps, I can follow.

--------------------------------------------------------------------------

Dear Frankjh19701, Good Morning.

Your example:

____A________B___C______D_____________E
1___2.35650___1___blank__=FORMULA 1___=FORMULA 2
2___2.45690___2___blank__=FORMULA 1___=FORMULA 2
3___2.53690___3___blank__=FORMULA 1___=FORMULA 2
4___2.11547___4
5___2.66354___5


DO THIS:

FORMULA 1
D1 -- =LARGE(A:A,1)
D2 -- =LARGE(A:A,2)
D3 -- =LARGE(A:A,3)

FORMULA 2
E1 -- =INDEX(B:B,MATCH(D1,A:A,0))
E2 -- =INDEX(B:B,MATCH(D2,A:A,0))
E3 -- =INDEX(B:B,MATCH(D3,A:A,0))

Try this one.

Please, tell me if it worked for you.
Feel free to ask anything about it.

Have a nice day.

Yes, that works. However, what do I do if I have duplicates? How can I show them all? I have two for Number one (Largest Value) but it's only showing the same number twice - the first one in the list not the second one.