Lookup formula help

J

jillteresa

Hi there,
I need to input a vertical formula that will retreive info based on a
code that could be on one of four tabs.

I'd like to develop a work order that will allow me to enter a code and
have all related creative information populate. For instance, promotion
code 1004 is in Column A on either the Q1, Q2, Q3 or Q4 tab. I need the
related creative name located in column G. The promotion code will
only appear once on only 1 tab.

Can I enter the vertical lookup formula to check all tabs and retreive
the information?

Jill :eek:
 
S

shail

Hi Jill,

Yes, you can check and retrieve the information using the VLOOKUP
function using nested IFs or better you use IFs and ORs.

Do send me a sample worksheet so that I can help you in it.

Thanks

Shail
 
T

Toppers

Try this:

=IF(ISNA(VLK1),IF(ISNA(VLK2),IF(ISNA(VLK3),IF(ISNA(VLK4),"No
match",VLK4),VLK3),VLK2),VLK1)

Where VLK1-4 are named formulae (do Insert=>Name=>Define==>Refers to:

For VLK1
Refer to: =VLOOKUP(Sheet1!$A1,'Q1'!$A:$G,7,0)

For VLK2
Refers to:=VLOOKUP(Sheet1!$A1,'Q2'!$A:$G,7,0)

etc

Assumes search parameter is in Column A of Sheet1 (A1 to start). Change
Sheet and column as needed. Copy down column as required.

OR replace the VLKn with the corresponding VLOOKUP statements in the formula.

HTH
 
Top