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):

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[TD]Column Etc...
[/TD]
[/TR]
[TR]
[TD]Row 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Location 1
[/TD]
[TD]Location 2
[/TD]
[TD]Location 3
[/TD]
[TD]Etc...
[/TD]
[/TR]
[TR]
[TD]Row 4
[/TD]
[TD][/TD]
[TD]Allocator Name 1
[/TD]
[TD]$
[/TD]
[TD]$
[/TD]
[TD]$
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 5
[/TD]
[TD][/TD]
[TD]Allocator Name 2
[/TD]
[TD]$
[/TD]
[TD]$
[/TD]
[TD]$
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 6
[/TD]
[TD][/TD]
[TD]Allocator Name 3
[/TD]
[TD]$
[/TD]
[TD]$
[/TD]
[TD]$
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Etc...
[/TD]
[TD][/TD]
[TD]Allocator Name Etc...
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[/TR]
[TR]
[TD]Row 1
[/TD]
[TD]Allocator Name
[/TD]
[TD]Location
[/TD]
[TD]$
[/TD]
[/TR]
[TR]
[TD]Row 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


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.