Finding a table value based on specified column and row headers

[NPetersen]

Hello,

I have a table that appears like this (The range may extend to further columns and rows later, but the headings will remain Location & Name):

	Column A	Column B	Column C	Column D	Column E	Column Etc...
Row 3			Location 1	Location 2	Location 3	Etc...
Row 4		Allocator Name 1	$	$	$
Row 5		Allocator Name 2	$	$	$
Row 6		Allocator Name 3	$	$	$
Row Etc...		Allocator Name Etc...

<TBODY>
</TBODY>

In another worksheet I have a list of data with a column set which includes:

	Column A	Column B	Column C
Row 1	Allocator Name	Location	$
Row 2
Row 3

<TBODY>
</TBODY>

What function/formula can I use in Column C to pull over the value corresponding to the allocator and location already entered in the corresponding row?

Thanks,

N

 

[BobUmlas]

=INDEX(Sheet1!$1:$1048576,MATCH(A1,Sheet1!$B:$B,0),MATCH(B1,Sheet1!$3:$3,0))

 

[JoeMo]

You could use a combination of INDEX and MATCH functions like this in sheet2 column C cells:

=INDEX(Sheet1!$B$3:$F$6,MATCH(A1,sheet1!$B4:$B6,0),MATCH(B1,sheet1!$C$3:$F$3,0))

where Allocator Name is A1 in the layout you show and Location is B1.

 

[NPetersen]

You could use a combination of INDEX and MATCH functions like this in sheet2 column C cells:

=INDEX(Sheet1!$B$3:$F$6,MATCH(A1,sheet1!$B4:$B6,0),MATCH(B1,sheet1!$C$3:$F$3,0))

where Allocator Name is A1 in the layout you show and Location is B1.

Thanks again JoeMo - Initially it was not grabbing the right cell but perhaps that could be a communication error on my part. I substituted "$C$3" with "$B$3" in the second MATCH function and it worked perfectly.


-N

 

[JoeMo]

Thanks again JoeMo - Initially it was not grabbing the right cell but perhaps that could be a communication error on my part. I substituted "$C$3" with "$B$3" in the second MATCH function and it worked perfectly.


-N

You are welcome - thanks for the reply.