Lookup and Match 1 row down...maybe

Alex O

Active Member
Joined
Mar 16, 2009
Messages
345
Office Version
  1. 365
Platform
  1. Windows
I'm trying to reformat an obscurely formatted sheet, to finish this up I need to find a way to match the value in A in J and return the corresponding value one row down in K. Most of the names only have one value in K, but there are a few that have 2 or more and I need all of them. Is this possible?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Thanks,<o:p></o:p>

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 253px"><COL style="WIDTH: 75px"><COL style="WIDTH: 292px"><COL style="WIDTH: 160px"><COL style="WIDTH: 28px"><COL style="WIDTH: 42px"><COL style="WIDTH: 85px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 407px"><COL style="WIDTH: 211px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; 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><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">1st Choice</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$120.01 </TD><TD>768 Hummingbird Ln</TD><TD>Branson</TD><TD>MO</TD><TD style="TEXT-ALIGN: right">65616</TD><TD>501-223-5801</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">101 Mower Mart 870-467-5452</TD><TD style="FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">A Classic Cut</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$88.24 </TD><TD>306 Sunny Gap Rd</TD><TD>Conway</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">72032</TD><TD>501-329-4282</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Mountain Home 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">A Perfect Bloom Florist & Interiors LLC</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$30.00 </TD><TD>407 Dewitt Henry Dr</TD><TD>Beebe</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">72012</TD><TD>501-882-2144</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">1st Choice 501-223-5801</TD><TD style="FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">AAA Rent-All & Sales Inc</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$67.30 </TD><TD>806 W Race</TD><TD>Searcy</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">72143</TD><TD>501-268-8445</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">West Little Rock 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">AAA Rent-All & Sales Inc</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$147.89 </TD><TD>806 W Race</TD><TD>Searcy</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">74143</TD><TD>870-793-2147</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">2 Moms + 4 Care/6 Days 501-985-9962</TD><TD style="FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">AAA Splurge Salon & Day Spa</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$61.68 </TD><TD>1502 W DeWitt Henry Dr Ste C</TD><TD>Beebe</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">72012</TD><TD>501-882-5011</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Jacksonville-Cabot 2010</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Ablution Day Spa</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$95.33 </TD><TD>4904 Central Ave</TD><TD>Hot Springs</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">71913</TD><TD>501-525-6450</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">24 Hour Drain Care & Maintenance 501-941-8781</TD><TD style="FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Ace Mufflers & Welding</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$60.00 </TD><TD>1301 W Pleasure</TD><TD>Searcy</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">72143</TD><TD>501-268-2170</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Conway 2009</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Affordable Residential Roofing</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$5.36 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Jacksonville-Cabot 2010</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Anderson's Piano Clinic Inc</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$12.00 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">24 Hour Drain Care & Septic 501-941-8781</TD><TD style="FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">A-OK Cool & Heat</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$75.56 </TD><TD>847 Fox Pass Cutoff</TD><TD>Hot Springs</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">71901</TD><TD>501-269-1964</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Lonoke 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Apex Termite & Pest Control</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$156.13 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Conway 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Associates In Behavioral Medicine</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$456.75 </TD><TD>301 S College Ste 200</TD><TD>Mountain Home</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">72653</TD><TD>870-405-7072</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">3 Phase Refrigeration Heating & Air 501-802-2501</TD><TD style="FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Automo Bills Inc</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$82.11 </TD><TD>211 High School Rd</TD><TD>Arkadelphia</TD><TD>AR</TD><TD></TD><TD>870-246-0458</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Arkadelphia 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Balancing Touch Wellness Center</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$234.00 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">8 Day Tires 501-697-2295</TD><TD style="FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Barnes Roofing</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$75.56 </TD><TD>1022 Rocking Chair Lane</TD><TD>Valley Springs</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">72682</TD><TD>870-449-5633</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Conway 2009</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Bentley's Restaurant</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$316.68 </TD><TD>7925 Edgemont Rd</TD><TD>Greers Ferry</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">72067</TD><TD>501-825-8884</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Conway 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Blue Poodle</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$75.56 </TD><TD>PO Box 22195</TD><TD>Hot Springs</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">71903</TD><TD>501-525-2112</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">8th Street Cafe & Bakery 870-425-5607</TD><TD style="FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Brenda Benish FIC LUTCF /DBA Modern Woodmen</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$953.00 </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-STYLE: italic; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">Mountain Home 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">By Design A Floral Studio</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Tahoma; FONT-SIZE: 8pt">$79.79 </TD><TD>310A Ouachita Ave</TD><TD>Hot Springs National Park</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">71901</TD><TD>501-609-9775</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">A Classic Cut 501-329-4282</TD><TD style="FONT-FAMILY: Tahoma; FONT-SIZE: 9pt; FONT-WEIGHT: bold"></TD></TR></TBODY></TABLE>
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
One more thing....I trying to return the value in H.

Thanks
 
Upvote 0

Forum statistics

Threads
1,224,574
Messages
6,179,634
Members
452,934
Latest member
Jdsonne31

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