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


--
CDM
------------------------------------------------------------------------
CDM's Profile: http://www.excelforum.com/member.php...o&userid=32339
View this thread: http://www.excelforum.com/showthread...hreadid=521007

http://www.excelforum.com/showthread...39#post1518439

Here's your answer


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=521007

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.



CDM wrote:

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

--
CDM
------------------------------------------------------------------------
CDM's Profile: http://www.excelforum.com/member.php...o&userid=32339
View this thread: http://www.excelforum.com/showthread...hreadid=521007

--

Dave Peterson

Use INDEX and MATCH functions.

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

HTH
--
AP

"CDM" a écrit dans le
message de ...

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


--
CDM
------------------------------------------------------------------------
CDM's Profile:
http://www.excelforum.com/member.php...o&userid=32339
View this thread: http://www.excelforum.com/showthread...hreadid=521007

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


--
CDM
------------------------------------------------------------------------
CDM's Profile: http://www.excelforum.com/member.php...o&userid=32339
View this thread: http://www.excelforum.com/showthread...hreadid=521007

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

CDM wrote:
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