How to get index of selected item in drop down list?

Hi - I am trying to have two drop down lists (created through the data validation menu) identify a specific row/column value which is then displayed in a specific box. So Imagine the simplest scenario:

B1 = "left, C1 = "right, A2 = "top", A3 = "bottom".

There is a drop down list at A5 that has A2:A3 as the options. And a drop down list at A6 that has B1:C1 as the options.

If left and top are chosen, than at A7 the data from B2 is displayed. If left and bottom are chosen, the data from B3 is displayed at A7. If right and top is chosen, then the data from C2 is displayed at A7. Finally, if right and bottom is chosen, the data from C3 is displayed at A7.

My thought is that I can somehow extract the indices of the selections of the combo boxes in A5 and A6 and use that to identify a cell. But I don't know how to do this.

Also note that this is a very simplified example. The actual spreadsheet I want to do this on has about 10 columns and about 100 rows.

Thanks so much!!!

nleahcim,

Attached is a sample workbook based on the criteria provided. I did expand it to 10 columns and 100 rows, though. I put the Row drop-down list in cell N2 and the Column drop-down list in cell N3. N4 contains the result using the following formula:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

nleahcim,

Alternate formula in cell N4 using VLookup:

Please Login or Register  to view this content.

~tigeravatar

Originally Posted by tigeravatar

nleahcim,

Attached is a sample workbook based on the criteria provided. I did expand it to 10 columns and 100 rows, though. I put the Row drop-down list in cell N2 and the Column drop-down list in cell N3. N4 contains the result using the following formula:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

That's awesome. Your method makes perfect sense and it works great! Thanks so much for the prompt and super helpful response!