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)))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.