Can't go past 2 vlookups

[bmwho71]

Hello, I have a workbook that has 7 data sheets and a front main sheet. I have it set up so when you type a "NAME" into a main cell on the main sheet, it will auto populate the main sheet with a bunch of data from the 7 data sheets. I'm needing it to go from one sheet to the next to find and fill the data.

I'm using the below formula but it will only work up to the first two vlookups. When I add the third, I receive an error message stating....

IFERROR(VLOOKUP('Golden Ticket'!B10,'Schacht~'!$C:$AF,FALSE),(VLOOKUP('Golden Ticket'!$B$10,'Goodsen~Barton'!$C:$AF,30,FALSE),(VLOOKUP('Golden Ticket'!$B$10,'Parks~Banks'!$C:$AF,30,FALSE),(VLOOKUP('Golden Ticket'!$B$10,'Maxey~Thurman'!$C:$AF,30,FALSE),(VLOOKUP('Golden Ticket'!$B$10,'Fox~Walker'!$C:$AF,30,FALSE),(VLOOKUP('Golden Ticket'!$B$10,'Hanesworth~Cordell'!$C:$AF,30,FALSE),(VLOOKUP('Golden Ticket'!$B$10,'Johnson~Cooley'!$C:$AF,30,FALSE))))))))

Golden Ticket B10 is the main cell on the main sheet you type in the "Name", after that are the sheet names/cells to vlook at, then column to view.

Anyone know why I can't go past two vlookups?

Any help would be appreciated.
Thank you

 

[etaf]

you would need an IFERROR for each vlookup()
IFERROR(VLOOKUP('Golden Ticket'!B10,'Schacht~'!$C:$AF,FALSE), iferror( (VLOOKUP('Golden Ticket'!$B$10,'Goodsen~Barton'!$C:$AF,30,FALSE), iferror( (VLOOKUP('Golden Ticket'!$B$10,'Parks~Banks'!$C:$AF,30,FALSE), iferror( etc

There maybe a better way to do that - But off the top of my head thats why , to answer the question directly

BUT NOTE the error is in the formula

VLOOKUP('Golden Ticket'!B10,'Schacht~'!$C:$AF,FALSE)
is missing the column to lookup
vlookup(cell to lookup, range to lookup, column to return, exact/near)

VLOOKUP('Golden Ticket'!B10,'Schacht~'!$C:$AF, Need a column number here ,FALSE)

 

[bmwho71]

Hello etaf. Thank you very much for responding, your time is most appreciated. I fell like the fool for missing the first error, thanks for catching. I put in your formula adding all the iferror( and it still doesn't work for me. Now it tells me I have too few arguments.

With double parentheses before vlookup
IFERROR((VLOOKUP('Golden Ticket'!B10,'Schacht~'!$C:$AF,30,FALSE),(iferror((VLOOKUP('Golden Ticket'!$B$10,'Goodsen~Barton'!$C:$AF,30,FALSE),(iferror((VLOOKUP('Golden Ticket'!$B$10,'Parks~Banks'!$C:$AF,30,FALSE),(iferror((VLOOKUP('Golden Ticket'!$B$10,'Maxey~Thurman'!$C:$AF,30,FALSE),(iferror((VLOOKUP('Golden Ticket'!$B$10,'Fox~Walker'!$C:$AF,30,FALSE),(iferror((VLOOKUP('Golden Ticket'!$B$10,'Hanesworth~Cordell'!$C:$AF,30,FALSE),(iferror((VLOOKUP('Golden Ticket'!$B$10,'Johnson~Cooley'!$C:$AF,30))))))))

Without double parentheses before vlookup
IFERROR(VLOOKUP('Golden Ticket'!B10,'Schacht~'!$C:$AF,30,FALSE),(iferror(VLOOKUP('Golden Ticket'!$B$10,'Goodsen~Barton'!$C:$AF,30,FALSE),(iferror(VLOOKUP('Golden Ticket'!$B$10,'Parks~Banks'!$C:$AF,30,FALSE),(iferror(VLOOKUP('Golden Ticket'!$B$10,'Maxey~Thurman'!$C:$AF,30,FALSE),(iferror(VLOOKUP('Golden Ticket'!$B$10,'Fox~Walker'!$C:$AF,30,FALSE),(iferror(VLOOKUP('Golden Ticket'!$B$10,'Hanesworth~Cordell'!$C:$AF,30,FALSE),(iferror(VLOOKUP('Golden Ticket'!$B$10,'Johnson~Cooley'!$C:$AF,30))))))))

Again, thank your if you have any other ideas.

 

[etaf]

i have broken each part into a new line


IFERROR(
VLOOKUP('Golden Ticket'!B10,'Schacht~'!$C:$AF,30,FALSE),
iferror(
VLOOKUP('Golden Ticket'!$B$10,'Goodsen~Barton'!$C:$AF,30,FALSE),
iferror(
VLOOKUP('Golden Ticket'!$B$10,'Parks~Banks'!$C:$AF,30,FALSE),
iferror(
VLOOKUP('Golden Ticket'!$B$10,'Maxey~Thurman'!$C:$AF,30,FALSE),
iferror(
VLOOKUP('Golden Ticket'!$B$10,'Fox~Walker'!$C:$AF,30,FALSE),
iferror(
VLOOKUP('Golden Ticket'!$B$10,'Hanesworth~Cordell'!$C:$AF,30,FALSE),
iferror(
VLOOKUP('Golden Ticket'!$B$10,'Johnson~Cooley'!$C:$AF,30,FALSE) , "Not in lookup"
) ) ) ) ) ) )


Just for simplicity - I added in a spreadsheet without the sheet names

NOTE your last vlookup did not have a false - is that what you wanted

Book5
	A	B
1
2	not in lookup
Sheet2

Cell Formulas
Range	Formula
A2	A2	=IFERROR( VLOOKUP(B10,$C:$AF,3,FALSE), IFERROR( VLOOKUP($B$10,$C:$AF,3,FALSE), IFERROR( VLOOKUP($B$10,$C:$AF,3,FALSE), IFERROR( VLOOKUP($B$10,$C:$AF,3,FALSE), IFERROR( VLOOKUP($B$10,$C:$AF,3,FALSE), IFERROR( VLOOKUP($B$10,$C:$AF,3,FALSE), IFERROR( VLOOKUP($B$10,$C:$AF,3,FALSE),"not in lookup" ) ) ) ) ) ) )

 

[bmwho71]

Hello etaf,

It worked. Thank you so very much!!!! Appreciated how you broke it down for me to see and understand. my thanks !!!

 

[etaf]

you are welcome