VLOOKUP across 7 worksheets

[DarrenK]

Hello,

I've really looked online and attempted to copy/paste but I'm having difficulty with this. I am trying to develop a vehicle database for my company so that all employee cars are documented (each tab represents a specific department). That's the easy part.

What I'm having issues with on an 8th tab, I have a cell that allows someone to enter in any license plate. My VLOOKUP formula (in another cell of course) brings back data on that vehicle (owner name, make, model, etc).

I am not sure how to copy/paste the headers from Excel 2011 so I'll do my best to explain.

Cell J25 on HOME tab is where the license plate can be entered.

My first attempt to make sure it was working was this:
=IFERROR(VLOOKUP(J25,Management!$A:$N,8,0),"") I'm fairly competent with simple VLOOKUPs.

Each worksheet has the following layout:
[TABLE="width: 527"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 527"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 7"]License Plate is column 1, Name is column 8

[/TD]
[/TR]
[TR]
[TD]License Plate[/TD]
[TD]Year[/TD]
[TD]Make[/TD]
[TD]Model[/TD]
[TD]Color[/TD]
[TD]State[/TD]
[TD]Notes Name[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]2008[/TD]
[TD]Ford[/TD]
[TD]Escape[/TD]
[TD]Black[/TD]
[TD]Ohio[/TD]
[TD] John[/TD]
[/TR]
[TR]
[TD]DEF456[/TD]
[TD]2008[/TD]
[TD]Jeep[/TD]
[TD]Commander[/TD]
[TD]Black[/TD]
[TD]Ohio[/TD]
[TD]Disability placard Heather[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now that above formula worked since I was only searching one worksheet.

My attempt at searching multiple sheets went as follows:
=IF(ISNA(VLOOKUP(J25,R&D!A2:R99,8,0)),IF(ISNA(VLOOKUP(J25,'Data Entry'!A2:R99,8,0)),VLOOKUP(J25,IT!A2:R99,8,0),VLOOKUP(J25,HR!A2:R99,8,0)),VLOOKUP(J25,Management!$A:$N,8,0))

Of course that was found from another source online and only let me incorporate 5 departments. I have 2 additional departments I have to add but I can't get the syntax correct. I tried using the above formula just to search 5 of the 7 sheets and I kept getting an #N/A error but no idea why. Not sure if it's the ISNA as I've never utilized that function. In researching, I'd also like to (if an invalid license is entered) return with a "License not found" message.

The 7 department tabs are: R&D, IT, Data Entry, HR, Management, QA, and Collections.

If anyone can assist with my convoluted explanation, it would be greatly appreciated.

*A completely separate question: I wanted to try using an array formula instead of the above formula to search all 7 worksheets as it looked slightly less complicated BUT after the license plate is entered, there would be a form control button that would run the VLOOKUP macro. (A little more user friendly for those unfamiliar with Excel arrays). Is it possible to assign the Control+Shift+Enter keystrokes to the button so that it would run the array formula needed to bring back the desired data?

 

[Trebor76]

I am trying to develop a vehicle database for my company so that all employee cars are documented (each tab represents a specific department).

But that's not a database works Having separate tabs with virtually the same data is rarely the way to go. An Access Db wold not have separate tables but a single table with a department field (column) that can be reported against. If you had all the data in one tab you could just have another report template (tab) that could bring in the info after a number plate has been selected from a drop-down.

That said have a look at this VLOOKAllSheets UDF by the late Dave Hawley.

HTH

Robert

 

[Scott T]

Try

Book1
	F	G	H	I	J	K
25					FBC123
26	Year	Make	Model	Color	State	Notes Name
27	2008	Honda	CRV	Blue	MO	Beth
home

Cell Formulas
Range	Formula
F27	=IFERROR(VLOOKUP($J$25,'R&D'!$A$1:$G$3,COLUMNS($E27:F27),0),"")&IFERROR(VLOOKUP($J$25,IT!$A$1:$G$3,COLUMNS($E27:F27),0),"")&IFERROR(VLOOKUP($J$25,'Data entry'!$A$1:$G$3,COLUMNS($E27:F27),0),"")&IFERROR(VLOOKUP($J$25,HR!$A$1:$G$3,COLUMNS($E27:F27),0),"")&IFERROR(VLOOKUP($J$25,Management!$A$1:$G$3,COLUMNS($E27:F27),0),"")&IFERROR(VLOOKUP($J$25,QA!$A$1:$G$3,COLUMNS($E27:F27),0),"")&IFERROR(VLOOKUP($J$25,Collections!$A$1:$G$3,COLUMNS($E27:F27),0),"")
G27	=IFERROR(VLOOKUP($J$25,'R&D'!$A$1:$G$3,COLUMNS($E27:G27),0),"")&IFERROR(VLOOKUP($J$25,IT!$A$1:$G$3,COLUMNS($E27:G27),0),"")&IFERROR(VLOOKUP($J$25,'Data entry'!$A$1:$G$3,COLUMNS($E27:G27),0),"")&IFERROR(VLOOKUP($J$25,HR!$A$1:$G$3,COLUMNS($E27:G27),0),"")&IFERROR(VLOOKUP($J$25,Management!$A$1:$G$3,COLUMNS($E27:G27),0),"")&IFERROR(VLOOKUP($J$25,QA!$A$1:$G$3,COLUMNS($E27:G27),0),"")&IFERROR(VLOOKUP($J$25,Collections!$A$1:$G$3,COLUMNS($E27:G27),0),"")
H27	=IFERROR(VLOOKUP($J$25,'R&D'!$A$1:$G$3,COLUMNS($E27:H27),0),"")&IFERROR(VLOOKUP($J$25,IT!$A$1:$G$3,COLUMNS($E27:H27),0),"")&IFERROR(VLOOKUP($J$25,'Data entry'!$A$1:$G$3,COLUMNS($E27:H27),0),"")&IFERROR(VLOOKUP($J$25,HR!$A$1:$G$3,COLUMNS($E27:H27),0),"")&IFERROR(VLOOKUP($J$25,Management!$A$1:$G$3,COLUMNS($E27:H27),0),"")&IFERROR(VLOOKUP($J$25,QA!$A$1:$G$3,COLUMNS($E27:H27),0),"")&IFERROR(VLOOKUP($J$25,Collections!$A$1:$G$3,COLUMNS($E27:H27),0),"")
I27	=IFERROR(VLOOKUP($J$25,'R&D'!$A$1:$G$3,COLUMNS($E27:I27),0),"")&IFERROR(VLOOKUP($J$25,IT!$A$1:$G$3,COLUMNS($E27:I27),0),"")&IFERROR(VLOOKUP($J$25,'Data entry'!$A$1:$G$3,COLUMNS($E27:I27),0),"")&IFERROR(VLOOKUP($J$25,HR!$A$1:$G$3,COLUMNS($E27:I27),0),"")&IFERROR(VLOOKUP($J$25,Management!$A$1:$G$3,COLUMNS($E27:I27),0),"")&IFERROR(VLOOKUP($J$25,QA!$A$1:$G$3,COLUMNS($E27:I27),0),"")&IFERROR(VLOOKUP($J$25,Collections!$A$1:$G$3,COLUMNS($E27:I27),0),"")
J27	=IFERROR(VLOOKUP($J$25,'R&D'!$A$1:$G$3,COLUMNS($E27:J27),0),"")&IFERROR(VLOOKUP($J$25,IT!$A$1:$G$3,COLUMNS($E27:J27),0),"")&IFERROR(VLOOKUP($J$25,'Data entry'!$A$1:$G$3,COLUMNS($E27:J27),0),"")&IFERROR(VLOOKUP($J$25,HR!$A$1:$G$3,COLUMNS($E27:J27),0),"")&IFERROR(VLOOKUP($J$25,Management!$A$1:$G$3,COLUMNS($E27:J27),0),"")&IFERROR(VLOOKUP($J$25,QA!$A$1:$G$3,COLUMNS($E27:J27),0),"")&IFERROR(VLOOKUP($J$25,Collections!$A$1:$G$3,COLUMNS($E27:J27),0),"")
K27	=IFERROR(VLOOKUP($J$25,'R&D'!$A$1:$G$3,COLUMNS($E27:K27),0),"")&IFERROR(VLOOKUP($J$25,IT!$A$1:$G$3,COLUMNS($E27:K27),0),"")&IFERROR(VLOOKUP($J$25,'Data entry'!$A$1:$G$3,COLUMNS($E27:K27),0),"")&IFERROR(VLOOKUP($J$25,HR!$A$1:$G$3,COLUMNS($E27:K27),0),"")&IFERROR(VLOOKUP($J$25,Management!$A$1:$G$3,COLUMNS($E27:K27),0),"")&IFERROR(VLOOKUP($J$25,QA!$A$1:$G$3,COLUMNS($E27:K27),0),"")&IFERROR(VLOOKUP($J$25,Collections!$A$1:$G$3,COLUMNS($E27:K27),0),"")

 

[DarrenK]

Thank you both!

I may have overcomplicated the situation. I will try using those formulas to keep things the way they are but if I can't get it working I'll try to consolidate everything into one sheet and see how that goes. Thanks again.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=VLOOKUP($J25,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!A2:A99"),$J25)>0,0))&"'!A2:R99"),8,0)

where SheetList refers to a range which lists the relevant department sheets one by one.

 

[DarrenK]

Awesome, I'll give that a try as well. Thank you all!