Can I run Excel LookUp on a single cell through multiple sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neil - 21 Mar 2006 13:15 GMT

I am using Excel 2003 SP2 and am trying to find a way to run a look up
function on a single cell, through multiple sheets, as opposed to a normal V
or H Look Up.

Does anyone out there know if this is possible and if so, how to do it?

Reply to this Message

Ardus Petus - 21 Mar 2006 13:22 GMT

You cannot do that with a =VLOOKUP() formula.

You will need some VBA code (User-defined function)

Cheers,
--
AP

> I am using Excel 2003 SP2 and am trying to find a way to run a look up
> function on a single cell, through multiple sheets, as opposed to a normal V
> or H Look Up.
>
> Does anyone out there know if this is possible and if so, how to do it?

Reply to this Message

Pete_UK - 21 Mar 2006 15:25 GMT

You can do it by combining a series of IFs, such as:

IF not present in Sheet1 table THEN IF not present in Sheet2 table THEN
IF not present in Sheet3 table THEN "not present" ELSE return Sheet3
value ELSE return Sheet2 value ELSE return Sheet1 value

Obviously, this is not an Excel formula, but hopefully you can see how
to construct a lookup to 3 different sheets.

Hope this helps.

Pete

Reply to this Message