extract data from a simple table

[lee_har]

Hi Everyone, i hope someone can help me with this today as i really need to have my project finished by tomorrow morning. what i need to do is have 1 cell return a specific value from an external table based on the contents of 2 other cells in the same row.
E.G in cell D10 i need look at cell D2 for a choice of 3 inputs (a,b,c) and also look at cell D4 for a choice of 14 inputs (1,2,3,4,5.........etc). then check the first column and first row of a table on a separate sheet and return the cell which shows the matching row and column
a b c
1 163 178 216
2 216 253 330
3 232 247 294
4 290 334 431
5 161 176 214
6 214 251 328
7 251 266 314
8 322 359 452
9 348 363 426
10 327 364 459
11 264 279 335
12 308 323 364
13 284 299 355
14 390 449 583

so if D2="b" & D4="7" then D10 would return 266.
i feel this should be really straight forward but i just can't work it out.......help
thanks in advance
Lee

[Pepe Le Mokko]

Does the attached help? searchtable.xls

[lee_har]

Thank You for the fast reply Pepe Le Mokko, what i neglected to tell you was that i also had to use the formula in a workbook of 10 sheets totalling approx 5000 lines.
i actually came up with an answer, albeit a messy one:
=IF(R4="A",VLOOKUP(C4,Sheet9!$A$2:$D$16,2),IF(R4="B",VLOOKUP(C4,Sheet9!$A$2:$D$16,3),IF(R4="C",VLOOKUP(C4,Sheet9!$A$2:$D$16,4))))

it seems to work ok but i'm sure it could be a bit simpler/shorter

thanks again

Lee

[Pepe Le Mokko]

Supposing your admitted input is A B or C in uppercase, try this

Please Login or Register  to view this content.

(Although I don't really understand why the INDEX/MATCH approach doesn't work)