Excel - Problem with multiple variables

[NealM]

Hi Guys - Hope someone knows a way round this. ! ! !

I have an Excel spreadsheet in which there are two cells that can have any one of 5 different letters in them. In a seperate cell I need to put one of the five letters, which is dependant upon the combination of letters from the other two cells . . ! ! ! !

To help with describing what I'm trying to do please see the image below.

http://img228.imageshack.us/img228/1553/excelproblem0xq.jpg

[smurfy]

You can do this with a nested VLOOKUP but you need to do an index of your column headers.

It's quite hard to explain how I do this so see the attachment:
Column I (which can be hidden later or colour the text white to make it invisible) contains a column index number for each of the headings in Row 3 because it can use the same headings in column B. If the Letters were different, you would need to create a small lookup table.

Now you can lookup the first letter in Clumn B and then index that lookup to the column index number indicated by the lookup of the second letter.

[NealM]

Thanks for the reply smurfy.

It reads like a foreign language to me :dizzy: :dizzy: but I'll see how I get on and let you know ! ! !

[smurfy]

LOL, I think part of it was... "...in Clumn B and..."

Just copy the formula into your cell and the index numbers into column I and it'll work.
:)

I didn't think my explanation was that bad for 1am ;)

[NealM]

I hadn't even noticed the spelling mistake (typo ! ! ) I'm just sat here in total awe ! !! http://img74.imageshack.us/img74/3701/bowdown7tp.gif http://img74.imageshack.us/img74/3701/bowdown7tp.gif http://img74.imageshack.us/img74/3701/bowdown7tp.gif

I have transposed the cell references to suit and all is fine.

Many, many; MANY thanks.

Just in an effort to learn. Can you explain what the last bit is doing. The bit circled in the picture below. You're saying look up D16 in the range B4 : I8 . . . . . . . . but then it's what happens after that, that I don't understand.


http://img100.imageshack.us/img100/1489/excelproblem19sh.jpg

[oracle128]

VLOOKUP (http://office.microsoft.com/en-us/assistance/HP052093351033.aspx).