Index match formula problem

Hi there, I struggle with my index/match formula - it only works for the first row.

I'm trying to auto-populate the values from column H ('BOM' sheet) into the column E ('Options' sheet) as long as there is a match in BOM Codes (please refer to the attached spreadsheet). I developed this formula:

=INDEX(BOM!H2:H2000,MATCH(B9,BOM!B2:B2000,0))

where BOM!H2:H2000 refers to the value source column, B9 to the specific code, BOM!B2:B2000 to the code list column.

In the 'Options' sheet, I would expect values 16, 1 & 1 in cells E9, E17 & E18. However, I only get a proper value in E9, even though E17 & E18 do not return #N/A value.

Do you have a clue what causes this problem?

Many thanks,
Kapi

The problem is with your data set. you cant have any blanks in the data. thats why u only getting 16 back. The other values , a couple 1's i think have a blank cell before them that basically cancels your index and match.

Hi, thanks for your reply. Unfortunately, putting data in those blank cells didn't help. Thanks for trying, tho.

ok it worked for me. but yea there were more issues than I highlighted.

your formula should be

=INDEX(BOM!$H$2:$H$2000,MATCH(BOM!B3,BOM!$B$2:$B$2000,0))

and your data set along with fulling in cells B8,B16 , G8, G16, with data , you also need your code # column. the numbers should be sequential. (100..101...102..etc

Thank you so much for pointing this out, Ford

I reckon I will be able to get it fixed now.

Best regards,
Kapi