Search code examples
excelexcel-formulaexcel-2007

EXCEL: Search for a value in a range and return its adjacent value in a particular column

this is my sample data

1   0   11  12  A
2   9   13  14  B
3   8   15  16  C
4   7   17  20  D
5   6   18  19  E

I need a excel formula which returns B when i input 14 or 13 or 9 or 2 similarly it should return me A if i input 1 or 0 or 11 or 12.

Can anyone please help me

I tried with INDEX and match functions like

=IFERROR(INDEX($E$1:$E$5,MATCH(G1,IF(($B$1:$D$5=G1)+($A$1:$A$5=G1), $B$1:$D$5),0)),"Not Found")

My input was 12. but its giving me Value not found , though the value is there

Solution

  • Another alternative approach using INDEX( ), MAX( ) & ROW( )

    enter image description here

    • Formula used in cell H2

    =INDEX($E$1:$E$5,MAX(--(H1=A1:D5)*(ROW(A1:D5))))

    Caveat: Since you have tagged as Excel-2007 don't forget to hit CTRL+SHIFT+ENTER while exiting the edit mode.