nested lookup

[drumsab]

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

<!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[Lewiy]

Not fully understanding, but you could start with:

=CONCATENATE(A1*1,B1*1,C1*1,D1*1,E1*1)

To give you a single number and then perform calculations on that number.

 

[Andrew Poulsom]

I may have misunderstood, but can't you just use?

=MAX(A1:E1)

 

[drumsab]

Unfortunately, the already established values for each potential number (1,2,3,4,5) has the least desireable status assocaited with 3, then 2, then 1 then 4 and finally 5.

This means I need to search for 3 first and if I find a 3 in any one of cells in range A1:E1, I need to return that value and I'm done.

If I don't find a 3 then I need to search for a 2 in the range A1:E1. If I find a 2 then I need to return a value of 2 and I'm done. If I don't find a 2 then I need to search for a 1 and so on (sequence to search is first for a 3, then 2, then 1, then 4 and if I don't find any of those numbers then the default answer will be a value of 5.

 

[Jonmo1]

This is not the prettiest formula I've ever written, but I think it solves the requirement posted.

=INDEX({3,2,1,4,5},MATCH(TRUE,ISNUMBER(SEARCH({3,2,1,4,5},A1&B1&C1&D1&E1&5)),0))

 

[Jonmo1]

Here's another one...
I don't quite like concatenating the five cells
A1&B1&C1&D1&E1
That sucks...


Try this,
Build a lookup table to convert the real values, to a ranked value
Then use a formula like

=INDEX(I2:I6,MATCH(MAX(LOOKUP(LEFT(A1:E1&5,1)+0,$I$2:$J$6)),J2:J6,0))

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	4	1	2		1		2		Real Value	Ranked Value
2									1	3
3									2	4
4									3	5
5									4	2
6									5	1
Sheet1

 

[Jonmo1]

There I go overcomplicating...
We can remove the index from that, and just use the match...

=MATCH(MAX(LOOKUP(LEFT(A1:E1&5,1)+0,$I$2:$J$6)),J2:J6,0)


Note, these are array formulas that require CTRL + SHIFT + ENTER

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G1</th><td style="text-align:left">=INDEX(<font color="Blue">{3;2;1;4;5;""},MIN(<font color="Red">IF(<font color="Green">ISNUMBER(<font color="Purple">MATCH(<font color="Teal">{3,2,1,4,5},A1:E1,0</font>)</font>),{1,2,3,4,5},6</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz