Vlookups from multiple worksheets

[SuzieHal]

Hi Guys,

Need a bit of advice on m vlookup.

I currently have the following formula =(IFERROR(VLOOKUP($A3,'Tracker 2013.xlsx]Open'!$A:$F,2,0),"")) which looks up various data in a different workbook. I need to extend this to look in to more than one worksheet if the data can not be found in "open".

I came up with the following

=(IF(NOT(ISERROR(VLOOKUP($A7,Open!$A:$Z,4,0))),VLOOKUP(Summary!$A7,Open!$A:$Z,4,0)</SPAN>

,(IF(NOT(ISERROR(VLOOKUP($A7,'On Hold'!$A:$Z,4,0))),VLOOKUP(Summary!$A7,'On Hold'!$A:$Z,4,0)</SPAN>

,(IF(NOT(ISERROR(VLOOKUP($A12,Pending!$A:$Z,4,0))),VLOOKUP(Summary!$A12,Pending!$A:$Z,4,0),"")))))) and while it worked in my testing spreadsheet its coming up blank when I try to apply it where its needed. Any ideas? Thanks, Suzie </SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[RonB1111]

You've left off the other workbook name in the new formula. Also you could continue to use the IFERROR function, like this:

=IFERROR(VLOOKUP($A3,'Tracker 2013.xlsx]Open'!$A:$F,2,0),IFERROR(VLOOKUP($A3,'Tracker 2013.xlsx]On Hold'!$A:$F,2,0),IFERROR(VLOOKUP($A3,'Tracker 2013.xlsx]Pending'!$A:$F,2,0),"")))

You may need to adjust the range and col index # in the above to suit your data.

 

[SuzieHal]

Hi Ron,

thanks for that.
The formula I have was based on vlookups in the same workbook, hence it didnt have the "tracker" souce in it.
But when I try to adapt it so it to look up info in the tracker workbook I either get a blank cell or "false" result. (range and column index are right)

Thanks,
Suzie

 

[RonB1111]

Is this the way you modified the formula I suggested
=IFERROR(VLOOKUP($A3,Open'!$A:$F,2,0),IFERROR(VLOOKUP($A3,On Hold'!$A:$F,2,0),IFERROR(VLOOKUP($A3,'Pending'!$A:$F,2,0),"")))

What is entered in A3? and is it listed in any of the 3 worksheets?
I noticed you are using A7 and A12 as lookup values in the 2nd formula in your original post - does the A3 in my formula need to be changed?

 

[SuzieHal]

Hi Ron,

A3 contains a vacancy number (P123), which is listed in the worksheets.
The number moves between the various worksheets, depending on the progress of the role, hence the need to be able to look up data from various sources,

A7 and A12 were just typos in the formula.

Thanks,
Suzie

 

[RonB1111]

It would help if you could post some sample data.

The formula will return the contents of the cell in Col B next to the matching "P123" on the 1st worksheet it finds a match. Please verify that the cell in Col B is not blank and also confirm that the P123 in A3 does not contain any spaces (and do the same in your Open, On Hold and Pending worksheets. The formula will return a blank if there is not a match in any of the 3 worksheets. Also make sure your 3 worksheet tabs are spelled the same as in the formula.