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
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!
https://www.dropbox.com/s/tg7owvq5215i7nl/Lookup Error.xlsx?dl=0
I'm familiar with VLOOKUPS and have used complex examples for many years, but everyone is human so maybe I'm missing something simple
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!
https://www.dropbox.com/s/tg7owvq5215i7nl/Lookup Error.xlsx?dl=0
