Multiple Vlookups

[RichJW]

Hi all,
I have an existing spreadsheet, which has had 3 new tabs added to it.
In the original tab (TAB-A), I need to perform a vlookup on the data in column A, looking in the 3 new tabs for the data. The thing that makes it a little complex, is that if it finds the data in the 1st new tab (TAB-B), I need it to paste data into columns U-AE of TAB-A. If it finds the data in TAB-C, then I need the data pasted into columns AF-AQ. If it finds the data in TAB-D, then I need the data pasting into columns AR-BM.
The data in column A will be unique, but could be in any of the 3 new tabs, so the formula will need to look at all tabs.

Is this even possible with a standard vlookup/index/match formula, or will it require a macro? Can the macro add the formula?

Thanks,
Rich

 

[RichJW]

Hi,

Any advice, please?

Thanks,
Rich

 

[FormR]

Hi, don't you just need 3 distinct VLOOKUP() functions - one that references Tab-B in U:AE, one that references Tab-C in AF:AQ and one that references Tab-D in AR:BM?

 

[RichJW]

Thanks FormR.
I guessed I'm confused, as I've got macros in this workbook to upload worksheets from other workbooks, format them, add 42 additional columns, but then how does adding a vlookup macro work, as the cell reference will change constantly?

For example, the top/first vlookup formula (cell U5) is =IFERROR(VLOOKUP($A5,'Tracker - T&M Dashboard'!$B$3:$P$100,4,FALSE),""). Cell V5 next to it will show =IFERROR(VLOOKUP($A5,'Tracker - T&M Dashboard'!$B$3:$P$100,5,FALSE),""). Cell U6 (below U5) will show =IFERROR(VLOOKUP($A6,'Tracker - T&M Dashboard'!$B$3:$P$100,4,FALSE),""). Cell V6 shows =IFERROR(VLOOKUP($A6,'Tracker - T&M Dashboard'!$B$3:$P$100,5,FALSE),"").
How would I write a macro to allow for the changes, all the way from cell U5 to BJ100? The data I want to lookup is always in column A.

Sorry - and thanks!

Rich

 

[FormR]

Hi, you could increment the col_index_num like this:

=IFERROR(VLOOKUP($A5,'Tracker - T&M Dashboard'!$B$3:$P$100,COLUMNS($U5:U5)+3,FALSE),"")

Or you could use INDEX(MATCH()) for example:

=IF(ISNUMBER(MATCH($A5,'Tracker - T&M Dashboard'!$B$3:$B$100)),INDEX('Tracker - T&M Dashboard'!E$3:E$100,MATCH($A5,'Tracker - T&M Dashboard'!$B$3:$B$100,0)),"")

 

[RichJW]

Thanks again - I do like the first one.

Cheers,
Rich