Column A VLOOKUP Issue

[Bob Todd]

I have a formula below that works great when I search column B through N, but when I change it to column A to N, I get a N/A error.

=VLOOKUP(B3,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B1:$N700"),B3)>0),0))&"'!$B1:$N700"),9,FALSE)

The format of the information in all columns is general.

Thanks for any help.
Bob

 

[arthurbr]

Is the column number in the range still correct ? Is it 9 or 10 ?

 

[Bob Todd]

Is the column number in the range still correct ? Is it 9 or 10 ?

Yes, its column 9 but I switch it to 10 when I changed the range to column A

 

[arthurbr]

OK Have you checked the Evaluate Formula tool ?

 

[Fluff]

Are the values you want to lookup in col A or B?

 

[Bob Todd]

There are some values in column A I would like to pull.

This is a workbook with a lot of tabs and I'd like to be agile enough to anticipate any requests including pulling some information from column A

 

[Bob Todd]

OK Have you checked the Evaluate Formula tool ?

What would that tool tell me?

 

[Fluff]

There are some values in column A I would like to pull.

In that case try

=IFNA(VLOOKUP(B3,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$B1:$N700"),B3)>0),0))&"'!$B1:$N700"),9,FALSE),VLOOKUP(B3,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A1:$N700"),B3)>0),0))&"'!$a1:$N700"),10,FALSE))

 

[arthurbr]

What would that tool tell me?

Perhaps try it so you will see what happens when the formula goes to work and where the error is

 

[Bob Todd]

In that case try

=IFNA(VLOOKUP(B3,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$B1:$N700"),B3)>0),0))&"'!$B1:$N700"),9,FALSE),VLOOKUP(B3,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A1:$N700"),B3)>0),0))&"'!$a1:$N700"),10,FALSE))

Thank you, that worked great!