Matrix Lookup

[C Brandt]

Hopefully this one will be fairly easy:

I have a matrix with names down the side and products across the top. I have
a report sheet that needs the information at the juncture of these two
variables.

Is this fairly easy?

Regards,

Craig

 

[Guest]

If your table is in B210 w/headers in column A and row 1 then, one way is

=INDEX(A110, MATCH("Name", A1:A10, 0), MATCH("Product", A11, 0))

 

[Gord Dibben]

Alternate method.

With names in A2:A10 and products in B1:H1

Insert>Name>Create. Checkmark top row and left column.

Formula would then be =name1 product4 to get the intersected value.


Gord Dibben MS Excel MVP

 

[C Brandt]

JMB & Gord Dibben:

Thanks for your input, they were right on. I used JMB's solution as the

matrix was not started in row 1 and, Gord, your solution would not work with
that senario.

Thanks again,

Craig

 

[Stan]

Alternate method.

With names in A2:A10 and products in B1:H1

Insert>Name>Create. Checkmark top row and left column.

Formula would then be =name1 product4 to get the intersected value.

Gord Dibben MS Excel MVP

Hello Gord:
I liked your solution and tried it with success, I would like to
expand it by having the names being dynamic.. (a name in a seperate
cell and the product in a different cell.) such as =a1 b1. is this
possible? is there a formula to do this?
I know how to use the index and match formulas but this could be a
much easier way to find the value in the appropriate cell.

thanks
Stan