Building Tables from List

[jamesg]

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]

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]

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]

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]

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)))