brightonrock
Dodgy Hamstrings
- Jan 1, 2008
- 2,482
I know NSC loves an excel puzzle...This may be quite easy for the resident geniuses but it's got me stumped!
I have a problem where I have two cells using validation so all that can be selected is the column headers and row headers of a table elsewhere in the sheet. I want in a third cell to place a formula which looks in those 2 cells, and then looks up the value in the table that corresponds with the row & column specified.
An example (but very much not real) table is as follows:
........A........B........C.......D
1 Club Home Away Third
2 BHAFC Blue Volt Red
3 Leeds White Grey Navy
4 Burnley Maroon Yellow Black
In this example the data validation is lower down in A6 & A7, A6 being the list of clubs, A7 being home, away or third kit.
In A8, I want the formula which looks in A6 & A7, and looks up the corresponding value in the table. I.e. BHAFC & Away, it looks in the table and returns 'Volt'.
I've tried a combination of vlookups/hlookups and some indexes and matches, but can't for the life of me get it working...any wizards able to point me in the right direction?
I have a problem where I have two cells using validation so all that can be selected is the column headers and row headers of a table elsewhere in the sheet. I want in a third cell to place a formula which looks in those 2 cells, and then looks up the value in the table that corresponds with the row & column specified.
An example (but very much not real) table is as follows:
........A........B........C.......D
1 Club Home Away Third
2 BHAFC Blue Volt Red
3 Leeds White Grey Navy
4 Burnley Maroon Yellow Black
In this example the data validation is lower down in A6 & A7, A6 being the list of clubs, A7 being home, away or third kit.
In A8, I want the formula which looks in A6 & A7, and looks up the corresponding value in the table. I.e. BHAFC & Away, it looks in the table and returns 'Volt'.
I've tried a combination of vlookups/hlookups and some indexes and matches, but can't for the life of me get it working...any wizards able to point me in the right direction?