Two dimensional lookup problem

[CDM]

I have a table similar to the below:

Red Blue Green
Small A B C
Medium D E F
Large G H I

I want to be able to enter a colour and a size in separate cells, and
then in another cell have a formula which tells me what the result is
(e.g. if I entered 'Medium' and 'Blue' the result would be 'E').

I assume the answer lies in combining a VLOOKUP with a HLOOKUP, but I
can't work out how to do it. I guess I would have to change the
criteria to be in alphabetical order as well (e.g. Large, Medium, Small
and Blue, Green, Red) for it to work.

Hope someone can help!

Thanks

Catherine

 

[davesexcel]

http://www.excelforum.com/showthread.php?p=1518439#post1518439

Here's your answer

 

[Dave Peterson]

Or you could use =index(match())

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions03.html

Look at examples 2 & 3.

 

[Ardus Petus]

Use INDEX and MATCH functions.

Here's an example: http://cjoint.com/?dknHTOWGNZ

HTH

 

[CDM]

Thanks for your help everyone - I have used Dave Peterson's suggestion,
and it works really well.

 

[unavailable]

Highlight your table and check Insert|Name|Create (note that Top row and
Left Column are checked). Then if the separate cells are E1 and F1,
enter in whatever output cell you choose =INDIRECT(E1) INDIRECT(F1)

Alan Beban