Some cells keep showing #N/A after VLOOKUP formula - all data correct

[alextairoboticsdrones]

Hi all,

Have an excel sheet which contains items database in one sheet and an "invoice creator" on another.

Once part numbers and quantities are pasted from an external source, what the formula does is extracts information from different cells from the database and populates it in the invoice creator with various rules (IFs, VLOOKUP and copy paste and combine).

So from 320 items i pasted 99 are just not being found by the same formula and keep showing up as #N/A.

Have gone step by step in the HELP > HELP ON THIS ERROR (verified record exists in the databaase, same cell format, no spaces and extra signs and etc) and still 99 items fall under #N/A.

Have also tried doing it in a new separate tab.

Attached a part of the report  - please see column C.

Will appreciate your assistance.

Thanks

[#2]

You seem to have 2117 rows of data but only look at 2018 rows in the VLOOKUP. Consider formatting the tabvle on H.S. sheet as a table to avoid this problem in future. All about tables: https://jkp-ads.com/articles/excel2007tables.asp

[#3]

@Jan Karel Pieterse is correct. Change the formula in cell D2 to this:

=VLOOKUP(B2,'H.S. sheet'!$B$2:$I$2117,8,0)

and copy the formula down