Auto-populating depending on two options

[mattyn]

Hi All

I cannot see how to work this out, but I suspect it is very straightforward. I have a table as shown.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]H[/TD]
[/TR]
</tbody>[/TABLE]

On another WS -
In cell B2 I will type a number from the horizontal row between 1 and 7
In cell C2, I will type a number from the vertical column between 1 and 6
In cell D2, I want to automatically populate the letter from the grid above...

For example..........

[TABLE="width: 500"]
<tbody>[TR]
[TD]Horizontal 1-7[/TD]
[TD]Vertical 1-6[/TD]
[TD]Grid[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

etc.

Entry in Col C and D will be via drop down list. What is the formula to auto populate Col D?

Kindest regards, and very many thanks

Matt

 

[Indystick]

As far as I can tell, it's a straightforward 2D lookup using INDEX & MATCH. I named your range of letters CODES (excluding the numbers in Row 1, Column A) so I don't have to switch between worksheets. Here is my formula for cell D2

=INDEX(CODES,C2,B2)

Note: because you're using numbers in C2 and B2 you don't need to have the numbers in Row 1, Column A because you're simply telling the lookup to get it from whatever (y,x) position using the numbers in C2 and B2 respectively.

 

[mattyn]

Brilliantly quick so thank you.

However the number i enter into B2 and C2 are relevant as they are the actual headers for the Row 1 and Column A, not simply column numbers. Does that make sense?

The solution you offered I think is counting column and row numbers in the array - I think.

 

[Fluff]

How about
=INDEX(test!B$2:H$7,MATCH(C2,test!A$2:A$7,0),MATCH(B2,test!B$1:H$1,0))

 

[Indystick]

You bet!

Yes, B2 and C2 matter because they tell you row and column. What I meant was the headers for your table don't matter in this setup.

Think of it like this: your table of letters is a 6 row x 7 column array. That means the y-coordinate (row) can be any number between 1-6, and the x-coordinate (column between 1-7). If you're supplying numbers to your match statements, you're saying get me this value at (y, x) where y and x are the numbers you specify.

I recreated your table exactly, the named range I created (CODES) was for cells B2:H7. In other words, the INDEX(MATCH()) statement ignores your header row and header columns altogether. It just says go get me the value at (C2, B2) where C2 and B2 are integers.

Now, if your headers were unique values, and not sequential numbers corresponding to the row and column), then you'd have to match values to the header values (See Mr. Fluff's example in the reply above this to see what I mean).

 

[mattyn]

I think I am really tired and need a rest! In my OP I tried to simplify the table for you - and me - to not confuse matters. The actual table I am trying to apply the formula to does not go 1 to 7 or 1 to 6, but rather 7 to 1 and 6 to 1, hence me getting errors!!
I have now made a simpler table in the hidden data worksheet that conforms to your formula, and as expected, it works a treat!

I need a beer - but just wanted to say very many thanks! I think I simply show my lack of knowledge at the clever stuff you guys create!

Awesome - thank you both fluff and Indystick!

 

[Fluff]

Glad it's working & thanks for the feedback.

With the formula I supplied it doesn't matter what order the the values are in either for the rows, or columns.