Index Match with return vales from multiple columns

[shank0630]

See attached. Trying to return a container quantity in Column C based on the products in Column A and Container Size selected in Column B. I can get the values from one column, but how do I get a value from across 4 columns, depending on the product and column head? Must be easy, but I'm not seeing it.

Any advice?

Thanks!

[modytrane]

can't open your file. can you post another copy?

[gfranco]

Hello.
In "D3" write and then drag

=OFFSET($E$2;MATCH(A3;$F$3:$F$17;0);MATCH(B3;$F$2:$J$2;0))

[shank0630]

I couldn't get your formula to work. I looked up some tutorials on the OFFSET function and got this formula to work. See attached. However, for whatever reason, it works as expected for C3 to C10. For C11 to C17 it gives erroneous figures. I broke the formula down and put the match formulas in columns K and L to get an idea of what's happening and I'm lost. Why does this formula break starting C11?

=OFFSET($E$2,MATCH(A3,$E$3:$E$17),MATCH(B3,$F$2:$I$2))

Thanks for pointing me to the OFFSET function!

[gfranco]

Hello.
You have omitted the last argument of the match function so the return is incorrect.
See the attachment.

[shank0630]

Very much appreciated!

[Phuocam]

Or try this:

=INDEX($F$3:$I$17,MATCH(A3,$E$3:$E$17,0),MATCH(B3,$F$2:$I$2,0))

or:

=VLOOKUP(A3,$E$3:$I$17,MATCH(B3,$E$2:$I$2,0),0)