vlookup range on multiple sheets?

Duane

Board Regular
Joined
Mar 14, 2002
Messages
229
Hello all,

I've got info in A1:G100 on sheet2 through
sheet5.

I want to write a vlookup formula on sheet1
that looks for a value on any sheet. There
will only be one instance of the value, but
it could be on sheet2, or sheet4, or...

Can this be done? If so, HOW?!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
On 2002-03-27 13:21, Duane wrote:
Hello all,

I've got info in A1:G100 on sheet2 through
sheet5.

I want to write a vlookup formula on sheet1
that looks for a value on any sheet. There
will only be one instance of the value, but
it could be on sheet2, or sheet4, or...

Can this be done? If so, HOW?!

Duane,

Are these "tables" (a) true lookup tables or (b) data that you want to treat as such?

If (a), I'd suggest to pull them together into a single table in a sheet of its own.

Aladin
 
Upvote 0
Example with 2 ranges; I named the ranges (rL1 and rL2) but it is not necessary to name the ranges.
Revise references as necessary. For additional ranges copy from "&" and edit as necessary.

=IF(AND(LEN(A8),COUNTIF(rL1,A8)),VLOOKUP(A8,rL1,2,0),"")&IF(COUNTIF(rL2,A8),VLOOKUP(A8,rL2,2,0),"")
 
Upvote 0
On 2002-03-27 13:45, Aladin Akyurek wrote:
On 2002-03-27 13:21, Duane wrote:
Hello all,

I've got info in A1:G100 on sheet2 through
sheet5.

I want to write a vlookup formula on sheet1
that looks for a value on any sheet. There
will only be one instance of the value, but
it could be on sheet2, or sheet4, or...

Can this be done? If so, HOW?!

Duane,

Are these "tables" (a) true lookup tables or (b) data that you want to treat as such?

If (a), I'd suggest to pull them together into a single table in a sheet of its own.

Aladin

Hey Aladin,

That's the method I figured I'd have to use, but I was REALLy hoping there was "some niftier way" to do it. Let's hope the folks in Redmond have plans to increase vlookup functionality (using negative integers for the column variable would also be much simpler than using INDEX and MATCH too, wouldn't it?!)

Using this approach in effect doublicates the data, which is no fun when having to send as an email attachment; but since it's going to sit on a shared drive, it's probably the simpler of the two methods...

_________________
Regards, Duane
This message was edited by Duane on 2002-03-27 14:12
 
Upvote 0
On 2002-03-27 14:02, Dave Patton wrote:

Example with 2 ranges; I named the ranges (rL1 and rL2) but it is not necessary to name the ranges.
Revise references as necessary. For additional ranges copy from "&" and edit as necessary.

=IF(AND(LEN(A8),COUNTIF(rL1,A8)),VLOOKUP(A8,rL1,2,0),"")&IF(COUNTIF(rL2,A8),VLOOKUP(A8,rL2,2,0),"")

Hey Dave,

Yep, that was a method to consider, but I was cringing at the thought of having to include a new string of functions for each sheet. It's fine when I've got a small few, but if this workbook was managing 35 ranges, I'd hate to have to keep track of where I was in the middle of writing out this formula!

Oh well, thanks, I'll keep this method in mind...

_________________
Regards, Duane
This message was edited by Duane on 2002-03-27 14:10
 
Upvote 0
Not that you'd necessarily want to, but you could use

=VLOOKUP(A1,INDIRECT("sheet"&COUNTIF(Sheet2!A1:A100,A1)*2+COUNTIF(Sheet3!A1:A100,A1)*3+COUNTIF(Sheet4!A1:A100,A1)*4+COUNTIF(Sheet5!A1:A100,A1)*5&"!$a$1:$g$100"),7,0)

given that you only have one value and your sheets are names as you described.
 
Upvote 0
for two sheets with referance on sheet1 and data on sheet2 and three

=IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE)),VLOOKUP(A2,Sheet3!A:B,2,FALSE),VLOOKUP(A2,Sheet2!A:B,2,FALSE))
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top