Vlookup not working

Dear Forum.

Hoping someone can help me with this.

Please see attached workbook. In Column H23:H110 I have a lookup formula. The column that it is looking at (D) has data Validation for entries that correspond to the 'Lookup cells' (I3:K9). For some reason it works fine if I select 'HL/LL/UO/Shared High & Ceiling' in column D, however if I select 'FHR' or 'Header' I get an '#N/A'. I have checked the format and it seems fine. When I used Formula Checking it seems to have an issue with the entry in D before it's even looking at the 'lookup cells'.

Please see D96 & H96 for this issue.

Any help would be very much appreciated as this is needed asap and I'm at a loss.

Thank you in advance.

I note that your VLOOKUP(...) function has omitted the 4th argument -- which specifies binary search/approximate match or exact match. When omitted, VLOOKUP() assumes TRUE for this argument, meaning it will use the approximate match algorithm which requires that the lookup values be sorted in ascending order. A lookup value that would come before the first entry (FHR comes before HL alphabetically), it returns N/A.

The solution is to use exact match FALSE or 0 for the 4th argument of the VLOOKUP function. help file: https://support.office.com/en-us/art...8-93a18ad188a1

Onedit: alternative solution would be to sort the lookup table by categories.