lookup using row & column heading.

[JRichardson]

Need help with a lookup formula using row & column heading:
a b c d
Loc 1 Loc 2 Loc 3 Loc 4
1 Cust 1 6 2 18 1
2 Cust 2 8 20 4 6
3 Cust 3 1 1 8 10
4 Cust 4 2 5 5 1

The formula I need would return the value where the row & column meet (ie.,
return the number where Cust 3 & Loc 2 intersect.

Thanks. jen

 

[Sheeloo]

Use
=INDEX($B$2:$D$5,MATCH("Cust 1",$A$2:$A$5,0),MATCH("Loc 3",$B$1:$E$1,0))
assuming Row 1 and Col A contain headers and data is in B25

 

[Bernard Liengme]

I have you data in A1:E4
In H1 I have "Cust2" and in I1 I have "Loc 2"
The formula to return the value 20 is:
=(INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(I1,B1:E1)))

 

[JRichardson]

Is there a way to lock in the cells you are matching without having to use
the name "cust 1" & "loc 3"? Would the formula look like this:
=INDEX($B$2:$D$5,MATCH(a1,$A$2:$A$5,0),MATCH(b3,$B$1:$E$1,0))

The spreadsheet is pretty large w/ locations & customers & I am trying to
get a the number of stores a customer has for each location. Hope that
makes sense. Thanks. The formula does work if I type in each name though.

 

[Sheeloo]

Yes, you are right...

replace "Cust 1" with a cell reference having one of the values in Col A...
and "Loc 3" with a cell refrence having one of the values in Row 1...

You will also have to expand the ranges -
$A$2:$A$5 to include all values in Col A
$B$1:$E$1 to all values in Row 1
$B$2:$D$5 to you data (without the headings) [2 and 5 should also match with
the last row and last column specified in the ranges above]

 

[JRichardson]

Thanks. I appreciate your help! This made my life much easier today. j

Yes, you are right...

replace "Cust 1" with a cell reference having one of the values in Col A...
and "Loc 3" with a cell refrence having one of the values in Row 1...

You will also have to expand the ranges -
$A$2:$A$5 to include all values in Col A
$B$1:$E$1 to all values in Row 1
$B$2:$D$5 to you data (without the headings) [2 and 5 should also match with
the last row and last column specified in the ranges above]

Is there a way to lock in the cells you are matching without having to use
the name "cust 1" & "loc 3"? Would the formula look like this:
=INDEX($B$2:$D$5,MATCH(a1,$A$2:$A$5,0),MATCH(b3,$B$1:$E$1,0))

The spreadsheet is pretty large w/ locations & customers & I am trying to
get a the number of stores a customer has for each location. Hope that
makes sense. Thanks. The formula does work if I type in each name though.

 

[RagDyer]

If your headers didn't have spaces between the text and numbers,
if they were single "words", you could use XL's intersection operator ... a
<space>.

=Cust3 Loc2

=Cust_3 Loc_2

This works if you set:
<Tools> <Options>
<Calculation> tab
to "Accept Labels In Formulas".