Challenge: vlookup not consistent

[kenjohnson_342]

I am doing a price change using a serial number of sorts for my lookup value. This file has over 4,000 lines and they all work just fine except for 3. I have seen this before but usually when my lookup value is a number only (without alpha). I usually work-around by adding an "x" before or after my lookup value and my table and that usually work. In this case I have alpha characters in my lookup values. I also don't want to build a work-around contingency for 3 lines when 4,000 others work fine. Sure it's just 3 lines and I could manually override, but I'm trying to learn why it's not working. Teach me to fish!

Here is a sample of my lookup values. The first 4 work fine, but the last 2 (6850 and 6860) do not. I have verified they are in fact in my table and do match exact (no extra spaces or characters).

lookup values located in D1501:D1506
FLUSH_6820_138_HCHARDWOOD_BIFOLD
FLUSH_6826_138_HCHARDWOOD_BIFOLD
FLUSH_6830_138_HCHARDWOOD_BIFOLD
FLUSH_6840_138_HCHARDWOOD_BIFOLD
FLUSH_6850_138_HCHARDWOOD_BIFOLD
FLUSH_6860_138_HCHARDWOOD_BIFOLD

formula:
=IFERROR(VLOOKUP(D1506,INDIRECT("'"&LEFT(D1506,FIND("_",D1506,1)-1)&"'!A:B"),2,FALSE),
IFERROR(VLOOKUP(D1506,INDIRECT("'"&LEFT(D1506,FIND("_",D1506,1)-1)&"'!C:D"),2,FALSE),
VLOOKUP(D1506,INDIRECT("'"&LEFT(D1506,FIND("_",D1506,1)-1)&"'!E:F"),2,FALSE)))

I am using a series of indirect and vlookups because the pricing is in 3 different tables on each sheet. The sheet names correspond with the first word of my lookup value. In this example "FLUSH" is the name of the sheet. Again the formula for all of the other lines work just fine, just these 2 giving me trouble.


What am I missing?


Thank you all!

 

[CaledoniaAZ]

When you say they do not work, do you mean that data isn't found or it's not the results you're expecting? Your formula worked for me.