VLOOKUP from a Matrix Table but matching multiple columns.

[BOB12345]

Hi,

I have a request if anyone can help.

In 1 Worksheet (WorksheetA) we have a large matrix of data where each cell is being be calculated using large formulae.

Similar to the example below but a lot more data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]35[/TD]
[TD]36[/TD]
[TD]37[/TD]
[TD]38[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]40[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]43[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In a separate worksheet (WorksheetB) I am trying to extract data from the matrix however the data being asked to be extracted could change. And I have to lookup and match values from 2 columns. similar to table below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Section[/TD]
[TD]Data[/TD]
[TD]Should Return in Data Col[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]?[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]2[/TD]
[TD]?[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[TD]?[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]?[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]4[/TD]
[TD]?[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2[/TD]
[TD]?[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So by looking up the Product and looking up the section in WorksheetB and matching it to the matrix in WorksheetA.

I know it can be done but am having a mind blank, any help would be so appreciated.

Many Thanks

 

[philwojo]

Try this in your data column on your 2nd sheet.

=INDEX(Sheet5!$A:$F,MATCH($B2,Sheet5!$A:$A,0),MATCH($A2,Sheet5!$1:$1,0))

Change the "Sheet5" references to whatever your sheet 1 is called. This also assumes you have the labels of 1,2,3,4,5 and A,B,C,etc. on the 1st sheet.

 

[KennyGreens]

Hi, phil's method should work, but here is another method that should work that uses the same assumption as phil's that the labels 1, 2, 3, etc. and A, B, C, etc. are on the 1st sheet in separate cells.

=INDIRECT(Sheet5!A1 & Sheet5!B1)