Lookup based on Column AND Row

[ablazes]

Hi,

Is there a way to create a Lookup function that references the headings of both, columns and rows?

For example, if my columns have names of spcific machines and in my rows, I have the teams that sold them, (data is the number of machines sold by a specific team) is there a way to for me to look what team sold what machine?

..............MACH1.............MACH2.............MACH5
team_a.........5........................20......................12
team_b.........14.......................2.......................0
team_l..........8........................70......................8

Thanks.

 

[NBVC]

Use Index and Match.

=Index($A$1:$C$4,MATCH(X1,$A$1:$A$4,0),MATCH(Y1,$A$1:$C$1,0)),

WHERE $A$1:$C$4 is the range of the whole table,
X1 houses the team name, you can replace this with actual name in quotes.
Y1 houses the machine name, you can replace this with actual name in quotes.

Change ranges and cell references to suit.

Note. If your table expands, you will have to adjust the upper limits of each of the Match functions to suit also.

 

[Aladin Akyurek]

Hi,

Is there a way to create a Lookup function that references the headings of both, columns and rows?

For example, if my columns have names of spcific machines and in my rows, I have the teams that sold them, (data is the number of machines sold by a specific team) is there a way to for me to look what team sold what machine?

..............MACH1.............MACH2.............MACH5
team_a.........5........................20......................12
team_b.........14.......................2.......................0
team_l..........8........................70......................8

Thanks.

=INDEX($B$2:$D$4,MATCH(Team,$A$2:$A$4,0),MATCH(Machine,$B$1:$D$1,0))

 

[NBVC]

Hi,

Is there a way to create a Lookup function that references the headings of both, columns and rows?

For example, if my columns have names of spcific machines and in my rows, I have the teams that sold them, (data is the number of machines sold by a specific team) is there a way to for me to look what team sold what machine?

..............MACH1.............MACH2.............MACH5
team_a.........5........................20......................12
team_b.........14.......................2.......................0
team_l..........8........................70......................8

Thanks.

=INDEX($B$2:$D$4,MATCH(Team,$A$2:$A$4,0),MATCH(Machine,$B$1:$D$1,0))

I don't see how mine is wrong.

 

[ablazes]

NBVC, Aladin,

Thanks! This worked perfectly!!

-Ablazes

 

[Brian from Maui]

I don't see how mine is wrong.

The values to be returned are in B2:D4, not A1:C4

The teams to match are in A2:A4 not A1:A4

Machines to match are in B1:D1 not A1:C1

Book2
	A	B	C	D
1		Mach 1	Mach 2	Mach 3
2	Team_A	5	20	12
3	Team_B	14	2	0
4	Team_C	8	70	80
Sheet1

 

[Brian from Maui]

I don't see how mine is wrong.

The values to be returned are in B2:D4, not A1:C4

The teams to match are in A2:A4 not A1:A4

Machines to match are in B1:D1 not A1:C1

Book2
	A	B	C	D
1		Mach 1	Mach 2	Mach 3
2	Team_A	5	20	12
3	Team_B	14	2	0
4	Team_C	8	70	80
Sheet1

 

[NBVC]

The OP was made aware to change ranges to suit his/her specific needs. No specific ranges were given, therefore the range I chose was a sample only to demonstrate the formula.

WHERE $A$1:$C$4 is the range of the whole table,
X1 houses the team name, you can replace this with actual name in quotes.
Y1 houses the machine name, you can replace this with actual name in quotes.

Change ranges and cell references to suit.

Note. If your table expands, you will have to adjust the upper limits of each of the Match functions to suit also.

 

[barry houdini]

Aladin,

is the INDEX/MATCH/MATCH construction more efficient than using VLOOKUP(or even HLOOKUP) and MATCH like this?

=VLOOKUP(Team,$A$2:$D$4,MATCH(Machine,$B$1:$D$1,0)+1,0)

 

[Aladin Akyurek]

Aladin,

is the INDEX/MATCH/MATCH construction more efficient than using VLOOKUP(or even HLOOKUP) and MATCH like this?

=VLOOKUP(Team,$A$2:$D$4,MATCH(Machine,$B$1:$D$1,0)+1,0)

Yes, somewhat. There are other considerations though... Like: VLOOKUP formulas often refer to the return range by hard-coded column numbers. INDEX/MATCH and LOOKUP, which are more general, avoid that trap, hence robust.