CODEWITHSUNDEEP
excelexcel-formula
Tyler
Tyler

Reputation: 616

How do I collect value from a column base on it's row data selected in another column?

enter image description here

How can I get Cell A5 to show the value A from cell range A1:A3 base on the furthest value from zero collected & shown in B5 that is selected from range B1:B3?

Upvotes: 1

Views: 44

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

=INDEX(A1:A3,Match(B5,B1:B3,0))

Explanation:

The match function is used to determine what row B5 is found in the range B1:B3. The 0 tells match to look for an exact match. It will return the first B5 value it find from the top if there are duplicate values of B5. Based on that row, Index will return the same row number from the top in the A select A range.

VLOOKUP will not work in this situation as the information to return is to the left of the column we would search in and vlookup can only look to the right.

Upvotes: 1

Related Questions