vlookup range on multiple sheets?

[Duane]

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?!

[Aladin Akyurek]

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

[Dave Patton]

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),"")

[Duane]

On 2002-03-27 13:45, Aladin Akyurek wrote:

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 ]

[Duane]

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 ]

[IML]

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.

[JWARD]

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))