nested lookup

**drumsab** · 09-02-2011, 08:09 AM

Hi,
Not sure what forumula / function will work for what I need to do.

I have 5 cells in one row. Each of the cells may have a number between 1 and 5 (it may also be blank) and the numbers will not be in any specific order. I need to do a search of those cells for specific numbers in a specific sequence (each number value is associated wtih a "status" of an activity associated with that cell).

For example,

A1 = 4, B1=1, C1=2, D1=blank, E1=1

The formula I need to create is in G1
The formula needs to be something like:
If A1:E1 = 3 then
place value of 3 in cell G1
ELSE
if A1:E1 = 2 then
place value of 2 in cell G2
ELSE
if A1:E1 = 1 then
place value of 1 in cell G2
ELSE
if A1:E1 = 4 then
place value of 4 in cell G2
ELSE
place value of 5 in cell G2

Any advice or suggestion on how to do this formula in cell G2 would be VERY MUCH appreciated

**NBVC** · 09-02-2011, 08:13 AM

when you say: If A1:E1 = 3, does that mean any one of those cells, or all of those cells have to be 3?

With your sample set, what should the result in G1 be and why?

are the conditions in that order? so stop after true?

**drumsab** · 09-02-2011, 09:55 AM

sorry for any confusion

Any one of the cells (A1:E1) could contain the value 3 or all of them could contain the value 3 or none of them could contain the value 3.

So let's say my lookup formula is in cell G1.
I first need to do a lookup for the range A1:E1 for the value 3. If I find a 3 in one of those cells then I need to return a value of 3 for cell G1 and I'm done.

If I don't find a 3 in range A1:E1 then I need to look for a 2 in range A1:E1. If I find a 2 in one of those cells then I need to return a value of 2 for cell G1 and I'm done.

If I don't find a 2 in range A1:E1 then I need to look for a 1 in the range A1:E1. If I find a 1 in one of those cells then I need to return a value of 1 for cell G1 and I'm done.

If I don't find a 1 in range A1:E1 then I need to look for a 4 in the range A1:E1. If I find a 4 in one of those cells then I need to return a value of 4 for cell G1 and I'm done.

If I don't find a 4 in range A1:E1 then I need to return a default value of 5 and I'm done.

**NBVC** · 09-02-2011, 10:04 AM

Try:

=IF(COUNTIF(A1:E1,3),3,IF(COUNTIF(A1:E1,2),2,IF(COUNTIF(A1:E1,1),1,IF(COUNTIF(A1:E1,4),4,5))))

**drumsab** · 09-02-2011, 10:14 AM

that works...thanks much !!!

**NBVC** · 09-02-2011, 10:21 AM

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save