Conditional Lookup on Unsorted Data

[Aaron.B]

Hello, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

I came up with this one off the cuff and wondered if there was a different way. Here are the pertinent details. <o></o>

• I have unsorted data.
• Column E-I are performing the same lookup using a different methods and sources. If they have "Unknown”, the lookup failed.<o></o>
• All successful lookups in E-I will result in the same data. <o></o>
• Column J is just looking for the first non-"Unknown" value and taking it, as any other successful lookup will be the same data. <o></o>
• The formula in column J is:

  {IFERROR(INDEX(E2:I2,MATCH(1,--(E2:I2<>"Unknown"),0)),"Unknown")}

  <o></o>

It felt a little clunky, so I wondered if I was over-complicating it.<o></o>

 

[venkat1926]

I wonder whether it would be easier to solve the problem if a small extract of the data sheet with formulas is posted. you can used html maker available in this newsgroup.

 

[Aaron.B]

The formula we are discussing would produce the "Account Column". I displayed it in "AccountFormula" so you could see what it would look like in this example.

<table cellspacing="0" border="1" id="ABB777"><tr style="background: #CCCCCC;"><th>SomeDatapoint</th><th>Lookup1</th><th>Lookup2</th><th>Lookup3</th><th>Lookup4</th><th>Account</th><th>AccountFormula</th></tr><tr><td>5tplt4</td><td>Unknown</td><td>Unknown</td><td>Unknown</td><td>SomeAccount2</td><td>SomeAccount2</td><td>{=IFERROR(INDEX(B2:F2,MATCH(1,--(B2:F2<>"Unknown"),0)),"Unknown")}</td></tr><tr style="background: #F5F5F5;"><td>ujuo7xap8</td><td>SomeAccount3</td><td>Unknown</td><td>SomeAccount3</td><td>SomeAccount3</td><td>SomeAccount3</td><td>{=IFERROR(INDEX(B3:F3,MATCH(1,--(B3:F3<>"Unknown"),0)),"Unknown")}</td></tr><tr><td>c392o</td><td>Unknown</td><td>SomeAccount4</td><td>SomeAccount4</td><td>SomeAccount4</td><td>SomeAccount4</td><td>{=IFERROR(INDEX(B4:F4,MATCH(1,--(B4:F4<>"Unknown"),0)),"Unknown")}</td></tr><tr style="background: #F5F5F5;"><td>wb1ard</td><td>SomeAccount5</td><td>SomeAccount5</td><td>SomeAccount5</td><td>Unknown</td><td>SomeAccount5</td><td>{=IFERROR(INDEX(B5:F5,MATCH(1,--(B5:F5<>"Unknown"),0)),"Unknown")}</td></tr><tr><td>h0l7jc8</td><td>SomeAccount6</td><td>SomeAccount6</td><td>Unknown</td><td>Unknown</td><td>SomeAccount6</td><td>{=IFERROR(INDEX(B6:F6,MATCH(1,--(B6:F6<>"Unknown"),0)),"Unknown")}</td></tr><tr style="background: #F5F5F5;"><td>qsofwkor3f</td><td>Unknown</td><td>SomeAccount7</td><td>SomeAccount7</td><td>SomeAccount7</td><td>SomeAccount7</td><td>{=IFERROR(INDEX(B7:F7,MATCH(1,--(B7:F7<>"Unknown"),0)),"Unknown")}</td></tr><tr><td>g8bgysl3</td><td>SomeAccount8</td><td>SomeAccount8</td><td>Unknown</td><td>Unknown</td><td>SomeAccount8</td><td>{=IFERROR(INDEX(B8:F8,MATCH(1,--(B8:F8<>"Unknown"),0)),"Unknown")}</td></tr></table>