If-Contain Vlookup Equation

[excelnoob927]

Hello, I could really use some help with creating an equation for what I am trying to do below:

1. Vlookup from A1 if it's not blank (i can do this)
2. If A1 is blank, then check to see what B1 says.
3. If B1 contains the string ENH, then run a vlookup in Tab1; If it contains CGE, then run a vlookup in Tab2; If ACZ, Tab3; If GPA, Tab4.

How can I write such an equation.

Thanks so much, in advance!
-Excelnoob927

[BadlySpelledBuoy]

You could achieve this with nested IFs and VLOOKUPs but it would be long and painful.
I'm sure there's a better solution but without seeing a sample workbook it's hard to say what it would be.

BSB

[bebo021999]

Hello, I could really use some help with creating an equation for what I am trying to do below:

1. Vlookup from A1 if it's not blank (i can do this)
2. If A1 is blank, then check to see what B1 says.
3. If B1 contains the string ENH, then run a vlookup in Tab1; If it contains CGE, then run a vlookup in Tab2; If ACZ, Tab3; If GPA, Tab4.

How can I write such an equation.

Thanks so much, in advance!
-Excelnoob927

Basically, try
=IF(A1<>"",VLOOKUP from A1,IF(ISNUMBER(SEARCH("ENH",B1)),VLOOKUP in TAB1,IF(ISNUMBER(SEARCH("CGE",B1)),VLOOKUP in TAB2,IF(ISNUMBER(SEARCH("ACZ",B1)),VLOOKUP in TAB3,IF(ISNUMBER(SEARCH("GPA",B1)),VLOOKUP in TAB4,"")))))

[martindwilson]

or maybe
=IF(A1<>"","vlookup from a1",CHOOSE(MATCH(TRUE,ISNUMBER(SEARCH({"ENH";"CGE";"ACZ";"GPA"},B1)),0),"vlook up in tab1","vlook up in tab2","vlook up in tab3","vlook up in tab4"))

[newdoverman]

If you find the formula syntax of the responses of bebo021999 and martinwilson difficult to follow, I created an example that uses both of their instructions using actual tab and cell references to make the examples less abstract....I hope.

In addition, I don't know if the formula for A1 is dependent upon the value in B1 and visa versa or are they independent of each other with the exception of the situation where A1 is blank?

I have attempted to show on the enclosed file the situation where A1 and B1 are interdependent and where they are not.

I used the basic formula outlines previously given by bebo021999 and martinwilson

I hope that this is a clarification and not an added confusion.