If value in column A, display content in same row column B Solved

Question

Hello,

I am facing the following situation in Excel 2013.
Using the LARGE function, I have extracted a number from column A, which I now have in cell D1. This cell D1 displays the number from cell A8, for example.
In cell E1, I would like to show the text in column B on the same row as the LARGE value, that is, the content of cell B8.

Of course, when my database is updated in the future, the LARGE value obtained in D1 will change, so I should not be working on cell A8 or B8, but it should be dynamic.

Do you think this is possible? I hope that someone can help me again: -)

Thank you.

Configuration: Windows / Firefox 52.0

Vaucluse · Answer

Hello

try with:

=VLOOKUP(D1,A:B,2,FALSE)

or directly:

=VLOOKUP(LARGE(A:A,1),A:B,2,FALSE)

note that if your large value is represented multiple times in A, you will only get the 1st found entry in B

best regards

--
The quality of the response mainly depends on the clarity of the question, thank you!

yclik · Answer

Good evening
in Column "C" to handle ties

=A1+ROW()/100000

in E

=INDIRECT("B"&MATCH(LARGE($C$1:$C$4,ROW());$C$1:$C$4;0))

instead of $C$4, use the number of the last row of the data

LARGE($C$1:$C$4,ROW()) to obtain the nth largest value with ROW()

MATCH to get the row number where the data is located

INDIRECT to get the data from column B

Fab · Answer

Hello Vaucluse and Yclic,

Thank you for your 2 responses. I managed to make both of your solutions work in the exact case I proposed to you.

I couldn't apply it as it is in my spreadsheet for the following reason. In fact, I was stuck because of VLOOKUP (and seemingly the INDIRECT function in the second solution you proposed) because for them to work, the value being searched must be the leftmost in the search range.

""""
Important: With VLOOKUP, the column that contains the value or cell reference you are searching for must be the leftmost column in the range.
https://support.office.com/en-us/article/Looking-up-values-using-the-VLOOKUP-INDEX-or-MATCH-function-7144ef3f-e322-4f95-9e96-f1d743270438
"""""
This was indeed the case in my example above but not in my spreadsheet which is more complex and organized differently (columns A and B are reversed).

I ended up using a combination of the INDEX and MATCH functions to work around this (which is what was advised in the case VLOOKUP was not applicable).

=INDEX($A$1:$A$4,MATCH(D1,$B$1:$B$4,0),1)
"
https://www.excel-pratique.com/en/functions/index_match.php
"

(thanks to Yclic for the trick of adding decimals which allowed me to manage large values of different ranks but with equal values).

There you go, I'm not sure if it's clear but I hope it can help others. In any case, it's all good for me.

Thanks again.

If value in column A, display content in same row column B

3 réponses

Old lullaby

Texture issues in enshrouded

Unable to connect to freebox os

Edit ad on leboncoin for free

Freebox wifi password

Computer tower that won't turn on

Blocked: are my messages still being delivered?

Video intercom inspector

Your computer can't use another display.

Fransat decoder stuck