Reverse two-way lookup

mgirvin

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,245
Office Version
  1. 365
Platform
  1. Windows
Dear Most Amazing Excelers In The World,

I know how to do two-way lookup with the INDEX and MATCH functions (example, =INDEX(B2:H11,MATCH(A6,A2:A11,0),MATCH(E1,B1:H1,0)) to lookup a value in a table given a value in the row range and a value in the column range. I also know how to use the space intersector to do a two-way lookup (example, =A6:H6 E1:E11).

My question is, is it possible to do the reverse. Instead of finding the row and column positional and then returning the value at the intersection of the two, is it possible to have the value at the intersection and then find the two values in the row and column positions? So this would mean somehow returning two values in two cells.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
mgirvin said:
Dear Most Amazing Excelers In The World,

I know how to do two-way lookup with the INDEX and MATCH functions (example, =INDEX(B2:H11,MATCH(A6,A2:A11,0),MATCH(E1,B1:H1,0)) to lookup a value in a table given a value in the row range and a value in the column range. I also know how to use the space intersector to do a two-way lookup (example, =A6:H6 E1:E11).

My question is, is it possible to do the reverse. Instead of finding the row and column positional and then returning the value at the intersection of the two, is it possible to have the value at the intersection and then find the two values in the row and column positions? So this would mean somehow returning two values in two cells.
Click to expand...

Wished that you also opted for providing a sample...

<TABLE style="WIDTH: 319pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=423 border=0 x:str><COLGROUP><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 22pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=29 height=17> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 26pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=34>x</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 27pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=36>y</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 29pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=38>z</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=94>Search Value</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>15</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>a</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>13</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>19</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>7</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Count</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=COUNTIF(B2:D5,G1)">2</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>b</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>15</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>5</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>5</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Values At:</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Row</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Col</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>c</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>8</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS(G$4:G4)<=$G$2,INDEX($A$2:$A$5,SMALL(IF($B$2:$D$5=$G$1,ROW($B$2:$D$5)-ROW($B$2)+1),ROWS(G$4:G4))),"")' x:arrayrange="G4">b</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS(H$4:H4)<=$G$2,INDEX($B$1:$D$1,SMALL(IF($B$2:$D$5=$G$1,COLUMN($B$2:$D$5)-COLUMN($B$2)+1),ROWS(H$4:H4))),"")' x:arrayrange="H4">x</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>d</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>11</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>5</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>15</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS(G$4:G5)<=$G$2,INDEX($A$2:$A$5,SMALL(IF($B$2:$D$5=$G$1,ROW($B$2:$D$5)-ROW($B$2)+1),ROWS(G$4:G5))),"")' x:arrayrange="G5">d</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS(H$4:H5)<=$G$2,INDEX($B$1:$D$1,SMALL(IF($B$2:$D$5=$G$1,COLUMN($B$2:$D$5)-COLUMN($B$2)+1),ROWS(H$4:H5))),"")' x:arrayrange="H5">z</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS(G$4:G6)<=$G$2,INDEX($A$2:$A$5,SMALL(IF($B$2:$D$5=$G$1,ROW($B$2:$D$5)-ROW($B$2)+1),ROWS(G$4:G6))),"")' x:arrayrange="G6"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS(H$4:H6)<=$G$2,INDEX($B$1:$D$1,SMALL(IF($B$2:$D$5=$G$1,COLUMN($B$2:$D$5)-COLUMN($B$2)+1),ROWS(H$4:H6))),"")' x:arrayrange="H6"> </TD></TR></TBODY></TABLE>

Data is in A1:D5, including the labels (fields)...

G1 is the search value, set to 15...

G2:

=COUNTIF(B2:D5,G1)

G4:

Control+shift+enter, not just enter...
Code: 
=IF(ROWS(G$4:G4)<=$G$2,
   INDEX($A$2:$A$5,SMALL(IF($B$2:$D$5=$G$1,
     ROW($B$2:$D$5)-ROW($B$2)+1),ROWS(G$4:G4))),"")

H4:

Control+shift+enter, not just enter...
Code: 
=IF(ROWS(H$4:H4)<=$G$2,
   INDEX($B$1:$D$1,SMALL(IF($B$2:$D$5=$G$1,
     COLUMN($B$2:$D$5)-COLUMN($B$2)+1),ROWS(H$4:H4))),"")
 
Upvote 0
Dear Aladin Akyurek,

Yes, I apologize for not providing more details. But this is one smaller part of a bigger problem that I am trying to solve, and I am only about 95% sure what the problem is and 40% sure what the solution might be. This reverse two-way lookup was just an interesting idea that might provide a lead in my own mind to a definition of the problem and solution. When I ask further questions, I will provide more details.

Your solution is amazing, especially how the ROWS(G$4:G4) is the “On Off” (True/False) switch for the IF and the second argument in the SMALL.

Aladin, what is great about you and others here at the MrAladin Discussion Board, I mean MrExcel Discussion Board, is that you always stretch my Excel abilities in new and wonderful directions!

Your formula worked like a charm!

I’ll post again later when I refine my problem and request of a solution.
 
Upvote 0
Dear Aladin Akyurek,

I have to also say about your solution: Using the SMALL to deal with the duplicate values is very clever!
 
Upvote 0
Dear Aladin Akyurek,

Thank you for your amazing solution, but I just ran into a problem. When I enter data like this (where the first 15 is in a row that is before the second 15):

x y z
a 13 19 15
b 15 5 5
c 9 8 4
d 11 5

It returns data like this:

Row Col
a x
b z


But it should look like this:

Row Col
a z
b x


Can you amend the formula to encorporate this twist?
 
Upvote 0
Try...

H4, confirmed with CONTROL+SHIFT+ENTER, and copied down:

Code: 
=IF(ROWS(H$4:H4)<=$G$2,
   INDEX($B$1:$D$1,MATCH($G$1,INDEX($B$2:$D$5,
      SMALL(IF($B$2:$D$5=$G$1,ROW($B$2:$D$5)-ROW($B$2)+1),
         ROWS(H$4:H4)),0),0)),"")

Hope this helps!
 
Upvote 0
Dear Domenic,

Wow! That is amazing. However, after 2 hours this Sunday morning, I am still tryimng to understand it. I have a question (and maybe more later):

What does the 0 (zero) as the column argument for the INDEX function do? If I take the zero out, I get a #REF error. If I put it in, I get a row of values. What is that zero doing?

Thanks do much!
 
Upvote 0
mgirvin said:
What does the 0 (zero) as the column argument for the INDEX function do? If I take the zero out, I get a #REF error. If I put it in, I get a row of values. What is that zero doing?
Click to expand...

As you've already discovered, when the column argument is zero, the values from the entire row is returned. In this case, the row from which the values are returned is determined by the SMALL function.

Thanks do much!
Click to expand...

You're very welcome!
 
Upvote 0
Dear Domenic and Aladin,

You have both helped me before, and once again, you have helped me here. In each case that you have helped me, I have learned so much!
 
Upvote 0
If you change the search value to 3, it returns the following values:
<title>Excel Jeanie HTML</title>
<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td> </td> <td>x</td> <td>y</td> <td>z</td> <td> </td> <td>Search Value</td> <td style="text-align: right;">5</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>a</td> <td style="text-align: right;">13</td> <td style="text-align: right;">19</td> <td style="text-align: right;">7</td> <td> </td> <td>Count</td> <td style="text-align: right;">3</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>b</td> <td style="text-align: right;">15</td> <td style="text-align: right;">5</td> <td style="text-align: right;">5</td> <td> </td> <td>Values At:</td> <td>Row</td> <td>Col</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>c</td> <td style="text-align: right;">9</td> <td style="text-align: right;">8</td> <td style="text-align: right;">4</td> <td> </td> <td> </td> <td>b</td> <td>y</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>d</td> <td style="text-align: right;">11</td> <td style="text-align: right;">5</td> <td style="text-align: right;">15</td> <td> </td> <td> </td> <td>b</td> <td>y</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td>d</td> <td>y</td></tr></tbody></table>
The values should be
<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="height: 17px;"><td>Search Value</td> <td style="text-align: right;">5</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>Count</td> <td style="text-align: right;">3</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>Values At:</td> <td>Row</td> <td>Col</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td> </td> <td>b</td> <td>y</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td> </td> <td>b</td> <td>z</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td> </td> <td>d</td> <td>y</td></tr></tbody></table>
How do you fix this?
 
Upvote 0
You must log in or register to reply here.

Similar threads

sparky2205
  • Solved
Absolute reference from a lookup
2
Replies
17
Views
709
Alex Blakenburg
A
D
  • Question Question
Lookup value based on two column criteria
Replies
3
Views
460
Cubist
Cubist
J
  • Question Question
Help with LOOKUP
Replies
1
Views
196
Joe4
Joe4
R
  • Question Question
Find J/N Value
Replies
1
Views
174
Fluff
Fluff
D
  • Question Question
Xlookup or similar
Replies
6
Views
122
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,912
Messages
6,175,341
Members
452,638
Latest member
Oluwabukunmi

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