Got something to say or just want fewer pesky ads? Join us... 😊

Calling the Excel gurus...



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?
 


Paul Reids Sock

Well-known member
Nov 3, 2004
4,458
Paul Reids boot
Could it be done as an If, And?

Such as =If(And(A6="BHAFC",A7="Away", C2,"N/A"))

Or something along those lines - not amazing at using And.

Would also imply there would be a hell of a lot of combinations you would have to type out
 


brightonrock

Dodgy Hamstrings
Jan 1, 2008
2,482
Could it be done as an If, And?

Such as =If(And(A6="BHAFC",A7="Away", C2,"N/A"))

Or something along those lines - not amazing at using And.

Would also imply there would be a hell of a lot of combinations you would have to type out
Yeah I was trying to avoid an IF/AND for that exact reason, it would be nested a hundred times and I'd go cross eyed trying to write that formula!

That's why I'm guessing a variation of some sorts on a lookup is the answer - if the column header is X and the row header is Y, return Z, with Z being the corresponding cell in the table. Not having much luck though.
 


Uh_huh_him

Well-known member
Sep 28, 2011
10,612
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?

=VLOOKUP(A8,$A$2:$D$4,(IF(A6=B1,2,IF(B8=C1,3,4))))
This should work in the example given.

I take it your real life example has many more columns than 3?

If so you could add the numbers 1 - N underneath your table and use HLOOKUP to call back the column indicator for the Vlookup

Like so

CLUB HOME AWAY THIRD
BHAFC BLUE VOLT RED
LEEDS WHITE GREY NAVY
BURNLEY MAROON YELLOW BLACK
1 2 3 4

Then the formula :
=VLOOKUP(A7,A1:D4,HLOOKUP(A8,A1:D5,5,FALSE),FALSE)

Should work

EDIT: HA! Smiley faces are : and D
 


brightonrock

Dodgy Hamstrings
Jan 1, 2008
2,482
=VLOOKUP(A8,$A$2:$D$4,(IF(A6=B1,2,IF(B8=C1,3,4))))
This should work in the example given.

I take it your real life example has many more columns than 3?

If so you could add the numbers 1 - N underneath your table and use HLOOKUP to call back the column indicator for the Vlookup

Like so

CLUBHOMEAWAYTHIRD
BHAFCBLUEVOLTRED
LEEDSWHITEGREYNAVY
BURNLEYMAROONYELLOWBLACK
1234

Then the formula :
=VLOOKUP(A7,A1:D4,HLOOKUP(A8,A1:D5,5,FALSE),FALSE)

Should work

EDIT: HA! Smiley faces are : and D
And you sir, are a gentleman and a scholar. You're right, the table is way bigger than that - it's actually a product costing matrix - but adding the numbers at the bottom and using hlookup/vlookup works great. I knew NSC would know the answer!!
 








Uh_huh_him

Well-known member
Sep 28, 2011
10,612
And you sir, are a gentleman and a scholar. You're right, the table is way bigger than that - it's actually a product costing matrix - but adding the numbers at the bottom and using hlookup/vlookup works great. I knew NSC would know the answer!!

Glad to be able to help..

If the table is massive, I'm told you may want to use INDEX rather than VLOOKUP.
 


Paul Reids Sock

Well-known member
Nov 3, 2004
4,458
Paul Reids boot
=VLOOKUP(A8,$A$2:$D$4,(IF(A6=B1,2,IF(B8=C1,3,4))))
This should work in the example given.

I take it your real life example has many more columns than 3?

If so you could add the numbers 1 - N underneath your table and use HLOOKUP to call back the column indicator for the Vlookup

Like so

CLUB HOME AWAY THIRD
BHAFC BLUE VOLT RED
LEEDS WHITE GREY NAVY
BURNLEY MAROON YELLOW BLACK
1 2 3 4

Then the formula :
=VLOOKUP(A7,A1:D4,HLOOKUP(A8,A1:D5,5,FALSE),FALSE)

Should work

EDIT: HA! Smiley faces are : and D

Way to sh*t all over my If/And suggestion!!
 



Paying the bills

Latest Discussions

Paying the bills

Paying the bills

Paying the bills

Albion and Premier League latest from Sky Sports


Top
Link Here