Lookup value with multiple criteria to report back another value

Hi Forum,

What I want to achieve is to be able to lookup a value in a cell containing text. The text for example is AxB.
In this cell i want to extract the first character, so "A".
I then want to use this value to lookup in a table with headers A,B,C,D,E
I want to report back the value that corresponds to the same row that the formula is in.

It's easiest to explain this with a worked example.

How about
=INDEX($B3:$F3,MATCH(LEFT(G$2),$B$2:$F$2,0))*INDEX($B3:$F3,MATCH(RIGHT(G$2),$B$2:$F$2,0))

I suppose you want this

Try in G13 and drag down and forward the following formula

Formula:

Please Login or Register  to view this content.

Thanks so much for the speedy responses! Fluff your formula works great for the 1x1's but breaks at 1x1x1.
Jose your formula works for all of the values so thanks very much for that!

By the way will this formula work for 1x1x1x1 such as AxBxCxD and 1x1x1x1x1 such as AxBxCxDxE?

Oops missed the 3 character requirement
=INDEX($B3:$F3,MATCH(LEFT(G$2),$B$2:$F$2,0))*INDEX($B3:$F3,MATCH(RIGHT(G$2),$B$2:$F$2,0))*IF(LEN(G$2)<4,1,INDEX($B3:$F3,MATCH(MID(G$2,3,1),$B$2:$F$2,0)))

By the way will this formula work for 1x1x1x1 such as AxBxCxD and 1x1x1x1x1 such as AxBxCxDxE?

No, both formulas use only two or three products.

Hi Julian

To perform to five products (AxBxCxDxE) you can use this formula

Formula:

Please Login or Register  to view this content.

(Sorry but I couldn't get a shorter one)

Thanks Bo_Ry and Jose augusto! Both formulas work brilliantly!
This is now solved!

Originally Posted by bebo021999

Alternative option with non array formula (simple Enter to confirm typing)

G3:

Please Login or Register  to view this content.

That formula does not seem to work. Seems to result in 0 for all cells.