adding multiple variables within Vlookup with indirect function

[Utnerd]

Thanks for reading this - I have been working on a spreadsheet for sometime now. I have been stumped at this point for a while and need help.

here is what I am trying to accomlish - I have up to 4 cells that will have a worksheet referenced in them. I then need to pull data from any of the said worksheets and put it into the first worksheet. This code works but only if I have one cell that I am pulling info from. I have tried OR statements with COUNTIF statements but no luck. Let me know if any of you have some ideas. Thanks.

=IF(AND(ISNUMBER(C17),COUNTIF(H9:K9,"?*")),VLOOKUP(C17,INDIRECT(H9&"!A1:Z1000"),8,FALSE),"NA")

the vlookup would be the same for all
the cells that reference other sheets are H9,I9,J9,K9
C17 contains the info I need to match and pull from said worksheets. - one last note, it is possible that information contained on the sheets could overlap I would like to take the lower of these values if possible. not a necessity but if possible wanted to ask.

Thanks in advance

 

[Perpa]

Thanks for reading this - I have been working on a spreadsheet for sometime now. I have been stumped at this point for a while and need help.

here is what I am trying to accomlish - I have up to 4 cells that will have a worksheet referenced in them. I then need to pull data from any of the said worksheets and put it into the first worksheet. This code works but only if I have one cell that I am pulling info from. I have tried OR statements with COUNTIF statements but no luck. Let me know if any of you have some ideas. Thanks.

=IF(AND(ISNUMBER(C17),COUNTIF(H9:K9,"?*")),VLOOKUP(C17,INDIRECT(H9&"!A1:Z1000"),8,FALSE),"NA")

the vlookup would be the same for all
the cells that reference other sheets are H9,I9,J9,K9
C17 contains the info I need to match and pull from said worksheets. - one last note, it is possible that information contained on the sheets could overlap I would like to take the lower of these values if possible. not a necessity but if possible wanted to ask.

Thanks in advance

Utnerd,
Why not just have 4 formulae on the main Summary worksheet in 4 different cells C18:C21 (one for each data sheet):

C18 =IF(AND(ISNUMBER(C17),COUNTIF(H9:K9,"?*")),VLOOKUP(C17,INDIRECT(H9&"!A1:Z1000"),8,FALSE),"NA")

C19 =IF(AND(ISNUMBER(C17),COUNTIF(H9:K9,"?*")),VLOOKUP(C17,INDIRECT(I9&"!A1:Z1000"),8,FALSE),"NA")

C20 =IF(AND(ISNUMBER(C17),COUNTIF(H9:K9,"?*")),VLOOKUP(C17,INDIRECT(J9&"!A1:Z1000"),8,FALSE),"NA")

C21 =IF(AND(ISNUMBER(C17),COUNTIF(H9:K9,"?*")),VLOOKUP(C17,INDIRECT(K9&"!A1:Z1000"),8,FALSE),"NA")
[Code/]

I assume that the 'COUNTIF(H9:K9,"?*")' applies to each formula. That is,  if C17 is a number, and if any of the 
four cells (H9, I9, J9, and K9)  have text, then do the VLOOKUP for each data sheet.

Then just use the MIN function to get the lowest of the 4:
[Code]
=MIN(C18:C21)
[Code/]
****************************

OR you might try this:
[Code]
=IF(AND(ISNUMBER(C17),COUNTIF(H9:K9,"?*")),MIN(VLOOKUP(C17,INDIRECT(H9&"!A1:Z1000"),8,FALSE),_
VLOOKUP(C17,INDIRECT(I9&"!A1:Z1000"),8,FALSE),VLOOKUP(C17,INDIRECT(J9&"!A1:Z1000"),8,FALSE),_
VLOOKUP(C17,INDIRECT(K9&"!A1:Z1000"),8,FALSE)),"NA")
[Code/]
The above is untested...so try it on a copy of your workbook before you use it on your Original workbook.

Perpa