Lookup col. header with row header and data

C

cappy95833

New Member
Joined
Apr 12, 2011
Messages
3
I have a workbook that has a table that is setup similar to a multiplication table but with col/row headers are names and values are raw data. I need to find the col header with just the row and the data from the correct col.

Does anyone know of a way this might be able to be done?

A1: row name (Row2)
A2: value (25)
A3: formula/result (Col3)

Sheet2:
<table border="1"> <tbody><tr><td> </td><td>Col1</td><td>Col2</td><td>Col3</td></tr> <tr><td>Row1</td><td>15</td><td>78</td><td>180</td></tr> <tr><td>Row2</td><td>7</td><td>18</td><td>25</td></tr> <tr><td>Row3</td><td>78</td><td>99</td><td>106</td></tr> <tr><td>Row4</td><td>8</td><td>22</td><td>36</td></tr> <tr><td>Row5</td><td>50</td><td>55</td><td>60</td></tr> </tbody></table>

Let me know if I need to explain further
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
cappy95833 said:
I have a workbook that has a table that is setup similar to a multiplication table but with col/row headers are names and values are raw data. I need to find the col header with just the row and the data from the correct col.

Does anyone know of a way this might be able to be done?

A1: row name (Row2)
A2: value (25)
A3: formula/result (Col3)

Sheet2:
<table border="1"> <tbody><tr><td> </td><td>Col1</td><td>Col2</td><td>Col3</td></tr> <tr><td>Row1</td><td>15</td><td>78</td><td>180</td></tr> <tr><td>Row2</td><td>7</td><td>18</td><td>25</td></tr> <tr><td>Row3</td><td>78</td><td>99</td><td>106</td></tr> <tr><td>Row4</td><td>8</td><td>22</td><td>36</td></tr> <tr><td>Row5</td><td>50</td><td>55</td><td>60</td></tr> </tbody></table>

Let me know if I need to explain further
Click to expand...
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=544><COLGROUP><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1422" width=40><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1365" width=38><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 4977" width=140><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1137" width=32><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3612" width=102><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 30pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=40> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2680522 class=xl65 width=64>Field-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Field-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Field-3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=38> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 105pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=140>Lookup Value</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 24pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=32> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=102>Column Field</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>FAD</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64 align=right>15</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64 align=right>78</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl69 width=64 align=right>180</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Field-1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>KAD</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64 align=right>25</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64 align=right>18</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl69 width=64 align=right>25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Row Lookup Value</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Field-3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>JAD</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64 align=right>78</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64 align=right>99</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl69 width=64 align=right>106</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>KAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>LAD</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64 align=right>8</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64 align=right>22</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl69 width=64 align=right>36</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Count</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>GAD</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64 align=right>50</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64 align=right>25</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>60</TD>

<TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR></TBODY></TABLE>

The sample range is: A1:D6. Rows and columns are given names.

Lookup values are in: F2 and F4.

F6, just enter:
Code: 
=COUNTIF(INDEX(B2:D6,MATCH(F4,A2:A6,0),0),F2)

H2, control+shift=enter, not just enter:
Code: 
=IF(ROWS($H$2:H2)<=$F$6,INDEX($B$1:$D$1,SMALL(IF($B$2:$D$6=$F$2,
    IF($A$2:$A$6=$F$4,COLUMN($B$1:$D$1)-COLUMN($B$1)+1)),
      ROWS($H$2:H2))),"")
 
Upvote 0
Thanks Aladin Akyurek. That is a good solution for when there are multiple columns that have the same value in the same row and you know the exact value of the data. I didn't explain that the data values that the people will be looking for won't be exact.

I found a solution but wouldn't have come to it without your input. Thanks Aladin Akyurek!

Code: 
{=INDEX(b1:d1,1,match(f2,INDEX(b2:d6,Match(f4,a2:a6,0),0),1))}
 
Upvote 0
cappy95833 said:
Thanks Aladin Akyurek. That is a good solution for when there are multiple columns that have the same value in the same row and you know the exact value of the data. I didn't explain that the data values that the people will be looking for won't be exact.

I found a solution but wouldn't have come to it without your input. Thanks Aladin Akyurek!

Code: 
{=INDEX(b1:d1,1,match(f2,INDEX(b2:d6,Match(f4,a2:a6,0),0),1))}
Click to expand...

=INDEX(B1:D1,1,MATCH(F2,INDEX(B2:D6,MATCH(F4,A2:A6,0),0),1))

does not rquire control+shift+enter. More importantly, the values in the reference INDEX(B2:D6,MATCH(F4,A2:A6,0),0) are not in ascending order, not in the sample anyways, for doing an approximate match. Any comments?
 
Upvote 0
Aladin Akyurek said:
=INDEX(B1:D1,1,MATCH(F2,INDEX(B2:D6,MATCH(F4,A2:A6,0),0),1))

does not rquire control+shift+enter. More importantly, the values in the reference INDEX(B2:D6,MATCH(F4,A2:A6,0),0) are not in ascending order, not in the sample anyways, for doing an approximate match. Any comments?
Click to expand...

Adding of the brackets in my reply was a mistake on my part. you are correct that the formula now does not require CTRL+SHIFT+ENTER.

In my org. sample and the actual data the values are in ascending order, thanks for pointing that out, I didn't notice that at first but the data is in ascending order.

Again, thanks for all of the help! I couldn't have done it without your input :)
 
Upvote 0
cappy95833 said:
Adding of the brackets in my reply was a mistake on my part. you are correct that the formula now does not require CTRL+SHIFT+ENTER.

In my org. sample and the actual data the values are in ascending order, thanks for pointing that out, I didn't notice that at first but the data is in ascending order.

Again, thanks for all of the help! I couldn't have done it without your input :)
Click to expand...

If the real data is in ascending order and there are no duplicates in the calculated reference the Index/Match formula is admissible.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
111
Peter_SSs
Peter_SSs
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
295
Cquake
C
M
  • Solved
VBA: Checking if a row exists with exact data in multiple columns
Replies
6
Views
801
mwl_y
M
M
  • Solved
Counting how much number is in ascending order in a row.
2
Replies
12
Views
710
Peter_SSs
Peter_SSs
A
  • Question Question
Find entries in a sheet available or not
2
Replies
10
Views
493
AFZAL SOHAIL
A

Forum statistics

Threads
1,224,527
Messages
6,179,345
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top