lookup using row & column heading.
 

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

lookup using row & column heading.
 

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 B2:D5

"JRichardson" wrote:

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

lookup using row & column heading.
 

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)))
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"JRichardson" wrote in message
...
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

lookup using row & column heading.
 

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" wrote:

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 B2:D5

"JRichardson" wrote:

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

lookup using row & column heading.
 

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" wrote:

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" wrote:

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 B2:D5

"JRichardson" wrote:

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

lookup using row & column heading.
 

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

"Sheeloo" wrote:

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" wrote:

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" wrote:

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 B2:D5

"JRichardson" wrote:

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

lookup using row & column heading.
 

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".

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JRichardson" wrote in message
...
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