VLOOKUP Only Returning Some Values otherwise #N/A but Index/Match Works

ohnow

New Member
Joined
Apr 27, 2017
Messages
39
Hello,
I'm familiar with VLOOKUPS and have used complex examples for many years, but everyone is human so maybe I'm missing something simple:confused: I'm using the VLOOKUP in a table and I'm new to using tables.

I've checked the 13 possibilities here, even though some were not related to #NA : http://howtovlookupinexcel.com/13-common-problems-with-vlookups/

I copied my original workbook and saved it as this Lookup Error file. I've removed most of the data from the original file leaving an example of a non-working and working VLOOKUPs.

In the DT BOMs sheet, column K are VLOOKUPS.

The FT All Inv sheet has the table of data the VLOOKUPs are pointing too. I'm using a dynamic named range formula I've used for years.

I've highlighted the to two cells of data green and filtered to that color in the FT All Inv sheet.

Other things tried:
Copying data and pasting as values so both sheets have the same data.

Instead of using this type of formula on both sheets to combine two cells, =I2&J2, I tried using =TRIM(SUBSTITUTE(I2&J2,CHAR(160),CHAR(32))).

Odd Behavior:
Using Index/Match instead of VLOOKUP works.

While working in this version of the file to reduce data, Excel locked up several times. I always went back to the last version I saved. I rebooted.

The FT All Inv sheet uses a dynamic range formula to be able to add/delete rows & columns. However, I cannot delete rows 392 through 10002. This was unnecessary data I wanted to remove before posting this file here. If I delete those rows, the working VLOOKUP in DT BOMs K3 turns to #NA even though that data is in the un-deleted and unmoved row 391. At first I thought it broke because the dynamic named range formula had columns changed from D:D to XFA:XFA for some reason I do not think I've seen before. After fixing it though, the VLOOKUP still doesn't work.

On the DT BOMs sheet, I can delete 1 table column to the left of the VLOOKUP and the LOOKUP results are the same. If I delete another column to the left, the working VLOOKUP changes to #N/A. Each time a column is deleted, the formula joining data of two cells and the VLOOKUP change correctly to the new columns so the references are still the same.


This one really has me stumped. Any help would be greatly appreciated!:eek:

https://www.dropbox.com/s/tg7owvq5215i7nl/Lookup Error.xlsx?dl=0
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top