Indirect using VLookup Formula error

[Tlyons]

=IFERROR(VLOOKUP(A8,INDIRECT('COLLECTIONS '! '"&$N3&"'!"'&"$D:$V"),8,FALSE),0) This one does not work

=IFERROR(VLOOKUP($C3,INDIRECT("'"&$N3&"'!"&"$D:$V"),8,FALSE),0) This one works fine

I am trying to get the same data to pull into another sheet - is it not possible to have the indirect table on a different worksheet or is my syntax wrong - I'm also wondering if an index, match might work better than a vlookup. Thanks in advance for any help.

 

[Trebor76]

is it not possible to have the indirect table on a different worksheet

Yes as that's what your second formula is doing it's just that the sheet name is in cell N3. To manually enter the sheet name in the formula, use this:

=IFERROR(VLOOKUP($C3,INDIRECT("'COLLECTIONS '!$D:$V"),8,FALSE),0)

Though there's no point in using INDIRECT that I can see doing this

HTH

Robert

 

[Tlyons]

The working formula is on a sheet called Collections and is looking at the customer name in C3 and matches it against another sheet that is named by the Indirect Column N and pulls back data from Column 8 in the Array on the named sheet.

All I am trying to do with the new formula is have the formula on my Summary sheet and look at column A to find customer name then go to sheet Collections and find that customer name in column C then go to where column N of that sheet directs it to pull back the data just like the first formula. Seems like just an extra step but may need index match instead of Vlookup due to the extra step. or I am putting in double quotes where I should be putting in single quotes or vice versa I just can't get the formula to work.

 

[Tlyons]

=INDEX('COLLECTIONS '!$A$1:$AB$500,MATCH(A8,'COLLECTIONS '!$C$1:$C$500,0,INDIRECT,"'"&$N3&"'!"&"$D:$V"),5,0))
I'm thinking it may look something like this but I'm not sure this is even close it obviously doesn't work soooo there is that lol

 

[Trebor76]

As you are looking left to right VLOOKUP will suffice but the lookup_value (first) parameter of that function can't be an entire column!!

 

[Tlyons]

=IFERROR(VLOOKUP($A8,'LISA MARIE'!$D$2:$AH$500,2,FALSE),IFERROR(VLOOKUP($A8,JOSH!$D$2:$AH$500,2,FALSE),IFERROR(VLOOKUP($A8,MCHARBONNEAU!$D$2:$AH$500,2,0),IFERROR(VLOOKUP($A8,KMARKHAM!$D$2:$AH$500,2,0),IFERROR(VLOOKUP($A8,LJONES!$D$2:$AH$500,2,0),VLOOKUP($A8,'BY INVOICE'!$D$2:AB500,2,0))))))

This formula actually works I was just trying to trim it down a bit and I know it can work but I just can't seem to figure it out. Thanks for the reply.