Index Match problem

[westend9876]

I have a list of values in column FM on Sheet1. I need to match these values from column FM Sheet1 with those in column A Sheet2 and populate column F Sheet1 with the values from column D Sheet2.

SHEET1
....F...............FM
Order.........Values
..................91001
..................91001
..................91001
..................90701
..................90701
..................90701
..................90077
..................90077
..................90077
..................90077
..................90049

SHEET2
A...................D
Values.........Order
90077............1
90701............2
90049............3

 

[NARAYANK991]

Hi ,

I am not sure I have understood you correctly , but the way you have duplicates in column FM , you will have duplicates in column F also ; is that OK ? if so , try this :

=IFERROR(INDEX(Sheet2!$D$2:$D$4,MATCH(Sheet1!FM2,Sheet2!$A$2:$A$4,0)),"")

I have assumed your data starts from row 2 in both sheets.

Narayan

 

[westend9876]

Thank you. Works great.

 

[vicmoment]

just curious, would a simple vlookup not work

say = IFERROR(VLOOKUP(sheet1!FM2,Sheet2!$A$2:$D$4,4,0),"")

 

[SirJB7]

Hi, vicmoment!
HLOOKUP & VLOOKUP searches are equivalent to INDEX + MATCH, so satisfying your curiosity, yes, in this case it'd work equally.
Regards!