PDA

View Full Version : [SOLVED] Building Tables from List



jamesg
01-24-2011, 10:06 PM
Hi All,

See attachment

Can anyone help me with the formula required to return the values from the list on the left into the proper table on the right. Referances are in green, results are in yellow.

Thanks

macropod
01-24-2011, 11:28 PM
Hi James,

Try the following array formula in K16:
=INDEX($G15:$G115,MATCH($J16&K$15,$A$15:$A$115&$B$15:$B$115,0))
Copy across to M16. Change the ranges to suit your needs.

Note: Array formulae are input with Ctrl-Shift-Enter.

Bob Phillips
01-25-2011, 01:19 AM
Here is an alternative without concatenation

=INDEX($G15:$G115,MATCH(1,($A$15:$A$115=$J16)*($B$15:$B$115=K$15),0))

also array-entered.

jamesg
01-26-2011, 06:51 PM
Hi Paul,

Worked a treat, Thanks. But how do I stop it returning a #N/A error. I tried using =IF(ISERROR(x)=TRUE),"",x) but it clagged on me.

James

macropod
01-26-2011, 07:22 PM
Hi James,

It becomes a case of doing something like:
=IF(ISERROR(INDEX($G15:$G115,MATCH($J16&K$15,$A$15:$A$115&$B$15:$B$115,0))),"",INDEX($G15:$G115,MATCH($J16&K$15,$A$15:$A$115&$B$15:$B$115,0)))