Vlookup multiple sheets

Balmer07

New Member
Joined
Feb 14, 2018
Messages
41
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,721
Office Version
2007
Platform
Windows
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

New Member
Joined
Feb 14, 2018
Messages
41
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?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,721
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,721
Office Version
2007
Platform
Windows
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>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
You are welcome.
Glad we were able to help.
 

Forum statistics

Threads
1,077,796
Messages
5,336,383
Members
399,079
Latest member
DeoW

Some videos you may like

This Week's Hot Topics

Top