#VALUE error on VLOOKUP of text

[craig_villines]

I am receiving a #VALUE error when doing a VLOOKUP on 5 different
workbooks in a single cell. Only one workbook contains the data I am
looking for each data value. I have used ISERROR, ISNA and have been
unsuccessful in returning data. any idea's??

THANKS!!

 

[Guest]

We'll need your vlookup formula...

 

[Guest]

You usually get #N/A or #REF from vlookups, when the range doesn't contain
your match criterion and when the column ref is invalid respectively. #VALUE
usually means you've asked it to eg add a number to a word...

=if(isna(vlookup1(),if(isna(vlookup2(),if(isna(vlookup3(),if(isna(vlookup4(),if(isna(vlookup(5),"Not
found",vlookup5())),vlookup4())),vlookup3())),vlookup2())),vlookup1())

is one (horrid) way to get what you want, where you replace vlookupx() with
the right vlookup formula.

The alternative is to perform each vlookup in a seperate cell and then apply
logice to those results.