Nested vlookups?

[momofgirls]

I need to write a formula into cell K82 that does the following:
do a vlookup where the lookup value is cell B82. The range is on sheet Sled, cells A$2:C$57, the column index number is 3 and an exact match. So the formula would be vlookup(B82,Sled!a$2:c$57,3,FALSE)
HOWEVER, if there is no matching lookup value for cell B82 on the SLED sheet, then I want it to do a lookup where the lookup value is cell A82 and the range is column B also on sheet Sled. I've got the data on the Sled sheet sorted by column A and then by Column B. The range that I am looking for the alternate lookup value is in cells B$2:C$57, the column index would still be 3 and an exact match. So the formula would be vlookup(A82,Sled!b$2:c$2,3,FALSE)
There is a good possibility that there may not be a matching value for either lookup value, and if that is the case, I would like the formula to return "New"
I have a basic idea of how to write the nested formula, but I can't get it all the way there to work. Can anyone help?

 

[Aladin Akyurek]

I need to write a formula into cell K82 that does the following:
do a vlookup where the lookup value is cell B82. The range is on sheet Sled, cells A$2:C$57, the column index number is 3 and an exact match. So the formula would be vlookup(B82,Sled!a$2:c$57,3,FALSE)
HOWEVER, if there is no matching lookup value for cell B82 on the SLED sheet, then I want it to do a lookup where the lookup value is cell A82 and the range is column B also on sheet Sled. I've got the data on the Sled sheet sorted by column A and then by Column B. The range that I am looking for the alternate lookup value is in cells B$2:C$57, the column index would still be 3 and an exact match. So the formula would be vlookup(A82,Sled!b$2:c$2,3,FALSE)
There is a good possibility that there may not be a matching value for either lookup value, and if that is the case, I would like the formula to return "New"
I have a basic idea of how to write the nested formula, but I can't get it all the way there to work. Can anyone help?

Assuming that the result is a text value, try...

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"New",
    VLOOKUP(A82,Sled!$B$2:$C$57,2,0),
    VLOOKUP(B82,Sled!$A$2:$C$57,3,0)))

Note 1. FALSE and 0 are the same things.

Note 2. Sled!$B$2:$C$57 cannot have a "column index number" of 3. Hence 2 in the formula.

 

[momofgirls]

I get the choose 1,2 or 3 (never knew I could do that)
Where does the report z, 255 come in?
Is that something new I have to create?
and THANKS so much for getting abck to me - I was way off doing iserror and isblank inside of nested if formulas

 

[Aladin Akyurek]

I get the choose 1,2 or 3 (never knew I could do that)
Where does the report z, 255 come in?
Is that something new I have to create?
and THANKS so much for getting abck to me - I was way off doing iserror and isblank inside of nested if formulas

REPT("z",255) creates a string of 255 z's. I think REPT abbrevs REPEAT.

LOOKUP looking up the big string of z's returns the last text value from the reference it's fed with. In the suggested formula, the reference is an array constructed by CHOOSE from New and the results of the two VLOOKUPs, something like:

{"New",#N/A,#N/A}

By the way, did you try the formula?

 

[momofgirls]

Hmm... I just tried it and it gave me back a response of "New" even though I went back and verified that there is a matching lookup value for B82. In this case,the first option should have been true and not the 2nd or 3rd

 

[Aladin Akyurek]

Hmm... I just tried it and it gave me back a response of "New" even though I went back and verified that there is a matching lookup value for B82. In this case,the first option should have been true and not the 2nd or 3rd

What do you get separately with:

=VLOOKUP(A82,Sled!$B$2:$C$57,2,0)

and

=VLOOKUP(B82,Sled!$A$2:$C$57,3,0)

respectively?