Lookup Multiple Columns to Produce Result

**SpAnKy** · 05-12-2010, 01:48 AM

I need a formula to lookup 3 columns cell data, map it to a table, then produce a result.

Ill try explain this as easily as possible

If column A = 0, E will produce a 0

If column A = 1, it will then look at column B and C and produce the tables results in E

**DonkeyOte** · 05-12-2010, 03:15 AM

You might want to elaborate on the logic used to generate the values in E.

It would seem that:

A = 0 then E = 0

else

C = 0 then E = 6

else

B = 0 then E = 0
B = 1 then E = 7
B = 2 then E = 2
B = 3 then E = 1

=IF(A1=0,0,IF(SIGN(C1),CHOOSE(B1+1,0,7,2,1),6))

If there is some other logic please detail it.

(it's not clear what happens for 1 - 0 - 0 ... is this 0 or 6 ?)

**oldchippy** · 05-12-2010, 03:17 AM

Hi,

Not enough detail - can you explain the logic of the results in column E?

**SpAnKy** · 05-12-2010, 08:41 PM

Ok i have changed the file to represetn exactly what im trying to achieve.

Basically I have a number in Extension, it has 3 values assigned to it in 9001,9002,9003.. I then want the AKYD column in Yellow to return the matching result from the AKYD column in the table to the right.

There is no logic in how these values are represented, It is how 2 different phone systems produce a ringing result on certain keys on a phone. Basically a table reference.

So if extension 2010 has 9001=1, 9002=2, 9003=1, then I need 2010 AKYD = 2

I hope this makes it more clear.

Thanks

**SpAnKy** · 05-12-2010, 11:24 PM

that works donkeyote. Thanks very much. Now I am having problems incorporatin that into another forumala, But I will make a new thread for that.

**DonkeyOte** · 05-13-2010, 04:02 AM

Given your use of XL2007 and the fact that the return values are numeric (AKYD values) you could also consider using SUMIFS, eg:

E2: =SUMIFS($P$2:$P$9,$L$2:$L$9,$B2,$M$2:$M$9,$C2,$N$2:$N$9,$D2)
copied down

You might find this a more logical approach and it requires no logic other than a reference table.