Vlookup multiple sheets

[Balmer07]

Hi,

I am currently working on a worksheet, and on the sheet there are 4 tabs which have the same layout but each is a different cost centre.

I need to do a check to see if one number on a master tabappears on any of the 4 cost centre tabs.

Basically I need a vlookup across the 4 tabs but in one cellon the master.

Hopefully this makes sense.

 

[James006]

Hi,

It is quite a common request ... 3D lookup formula ...

Below is a link for a good explanation :

https://www.ablebits.com/office-addins-blog/2015/12/09/excel-3d-reference-formula/

Hope this will help

 

[Joe4]

It is quite a common request ... 3D lookup formula ...

Below is a link for a good explanation :

https://www.ablebits.com/office-addi...rence-formula/

James,
I was looking at that article, specifically the section named "List of functions supporting 3D references", and I do not see VLOOKUP, INDEX, or MATCH listed.
And I do not see any example of 3D lookups.
Am I missing something?

 

[DanteAmor]

Hi,
I am currently working on a worksheet, and on the sheet there are 4 tabs which have the same layout but each is a different cost centre.
I need to do a check to see if one number on a master tabappears on any of the 4 cost centre tabs.
Basically I need a vlookup across the 4 tabs but in one cellon the master.
Hopefully this makes sense.

You could expand the search detail a bit.
Assuming the following case:
On sheet 1 in cell A5 you enter data.
- In cell B5 you want the formula.
- The formula should look on each sheet.
- In which column should you search?
- What column do you want as a result?
- Only the first data found should be taken?
- Can the search data exist in several cells?
- Can the data to be found exist in several sheets?

 

[Balmer07]

So hopefully this will help

On my master sheet I have a column full of numbers

Tabs 2-4 I have identical layouts but each are individualcost centres so cannot put into one sheet.

So I need to look up if the number on the master tab is incolumn ‘L’ of each of the tabs 2-4 and if so pull back the number into themaster sheet.

Does this help?

 

[Joe4]

I supposed one way to do it would be to nest 3 levels of IFERROR/VLOOKUP formula, kind of like what I did here (but for only 2 sheets): https://www.mrexcel.com/forum/excel...-across-two-different-sheets.html#post5332616. I doubt that is the most efficient method, but it should work.

 

[DanteAmor]

So hopefully this will help
On my master sheet I have a column full of numbers
Tabs 2-4 I have identical layouts but each are individualcost centres so cannot put into one sheet.
So I need to look up if the number on the master tab is incolumn ‘L’ of each of the tabs 2-4 and if so pull back the number into themaster sheet.
Does this help?

It is not clear to me.


You could give an example.
Where do you want the formula, on which sheet, in which cell?
And from there we start.

 

[DanteAmor]

You need something like this:

Master Sheet

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:150.18px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Number</td><td >Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1234</td><td >Does not exists</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IFERROR(VLOOKUP(A2,Sheet1!L:M,2,0),IFERROR(VLOOKUP(A2,Sheet2!L:M,2,0),IFERROR(VLOOKUP(A2,Sheet3!L:M,2,0),IFERROR(VLOOKUP(A2,Sheet4!L:M,2,0),"Does not exists"))))</td></tr></table></td></tr></table>

 

[Balmer07]

IFERROR formula worked for what I needed. Thanks guys

 

[Joe4]

You are welcome.
Glad we were able to help.