Finding a value in a 2D table

[RRinTetons]

Excel 2010

Given a table that looks like this:

     A   B     C     D     E     F
   --------------------------------
 1|             0     1     2     3
 2|  100
 3|      4001   6     8     0     5
 4|      4002   7     1     7     6
 5|      4003   8     5     1     4
 6|  200
 7|      4001   7     3     3     6
 8|      4002   5     4     2     9
 9|      4003   9     2     8     7

And another table that looks like this:

100   4001   1   _______
200   4002   3   _______

What formula would work to fill in the field value? i.e. - the values 100,4001,2 would return a reference to E3 (value = 0)

I could do an unpivot, but that's a manual operation and this sheet is for users, not me, and I need to use the looked up value in a calculation in that cell, so the unpivot is awkward.

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Hi,

You need a proper table and not knowing the names of your fields...
[tt]
Field1 Field2 Field3 Field4 Field5 Field6
100 4001 6 8 0 5
100 4002 7 1 7 6
100 4003 8 5 1 4
200 4001 7 3 3 6
200 4002 5 4 2 9
200 4003 9 2 8 7
[/tt]
the formula, using named ranges
[tt]
=INDEX(OFFSET($A$1,MATCH(H2,Field1,0),2,COUNTIF(Field1,H2),4),MATCH(I2,OFFSET($A$1,MATCH(H2,Field1,0),1,COUNTIF(Field1,H2),1),0),J2+1)
[/tt]
where H2 & I2, j2 contain the the values 100,4001,2.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

If you absolutely must use the table layout you first described, it is possible (but a lot less easy than if you adopt Skip's very sensible suggestion).

A formula that will do the job with the original layout is:

=INDEX(A:E, MATCH(H2,OFFSET(B1,MATCH(G2,A:A, 0),0,100,1),0)+MATCH(G2,A:A, 0), MATCH(I2,1:1, 0))

I assume your table is in columns A to E, with the first two look-up parameters in columns A and B, and the row-look-up parameter in row 1. I've put the values to be looked-up in cells G2, H2 and I2. I've also assumed that there are no more than 100 values in column B before a new value in column A, but this can be scaled up as desired.

The formula first finds the row-number of the first look-up in column A. It then defines an area starting at this row in column B, and searches this area for the second look-up, to get the actual desired row-number. Then it finds the column-number for the third look-up. Finally it returns the value at the intersection using INDEX.

But Skip's approach is infinitely more professional.

 

[zelgar]

Another way....

Name the range of cells associated with the values in col A (e.g., "one" for range b3:f5, "two" for range b&:e9)
=IF(A20=100,VLOOKUP(B20,one,2+C20),VLOOKUP(B20,two,2+C20))

if you have more than 2 ranges...
modify the formula to the following
=VLOOKUP(B20,CHOOSE(A19/100,one,two,three),2+C20)

 

[zelgar]

or if you don't want to name the ranges...

=VLOOKUP(B19,CHOOSE(A19/100,b3:f5,b7:f9),2+C19)

adding additional ranges as needed in the CHOOSE section

 

[RRinTetons]

OK, now I've got three different ways to do it. I'll try Skip's away first to see if that can be adopted to my needs, if not I'll try the others.

-
Richard Ray
Jackson Hole Mountain Resort