Need Help Please=)

DGuy92034

New Member
Joined
Sep 10, 2014
Messages
5
I want to be able to put in an account number in an empty cell and have it pull the contents from a row that contains the account number.

If enter the account number into the empty cell, it will go find the account number on a separate tab and return with all the information in that row. Is this possible?

THANKS!
 

Some videos you may like

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"

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
You might need the VLOOKUP function but without much info, thats the best i can say :)
 

ExcelMercy

Board Regular
Joined
Aug 11, 2014
Messages
151
Sample data we can help you write it.

Vlookup or Match/Index sounds like they would both work.
 

DGuy92034

New Member
Joined
Sep 10, 2014
Messages
5
I have a excel workbook that contains 2 sheets. The first sheet, in cell A1, I want to put and account number. After, I enter the account number (and hit enter), I want it to find the account number on the second sheet and return all the information in that row (from the 2nd sheet) back to the first sheet, in Row 2.
 

DGuy92034

New Member
Joined
Sep 10, 2014
Messages
5

ADVERTISEMENT

It would find the account number in the 4th column and return the contents of the row that contains the account number, sorry kind of new to excel=)

DAILY COUNTCALLER NAMETIME OF CALLACCOUNT NUMBERGML REF #CLIENT ID/ NAMEZIPPICK UP LOCATIONFSR NAMETIME OF DISPATCHCONFIRMATION FROM FSRDELIVERY LOCATIONPICK UP TIMEDELIVERY TIME
1 10 33606HEART CARE CENTER 14320 BRUCE B DOWNS ** IN BILLING OFFICE TPA LAB
1 66008140 32570FL Dept of Health - 5527 Stewart St, Milton, FL Milton Patient Service - 5950 Berryhill Rd, Milton, FL
1 10 33606QUEST 4225 E FOWLER AVE TO: USF STUDENT HEALTH SERVICES 4107 CEDAR HILLS DRIVE STE 100 TGH
<colgroup><col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <col width="197" style="width: 148pt; mso-width-source: userset; mso-width-alt: 7204;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <tbody> </tbody>
 

ExcelMercy

Board Regular
Joined
Aug 11, 2014
Messages
151
Do you want all the contents from sheet2's column associated with the account number condensed to a single column on sheet1?

Or do you want all the accompanying information to have their own column on sheet1?
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
For columns to the right of the Account number column, A vlookup would work, for those to the left of that column, a vlookup would fail and you have to resort to INDEX-MATCH

I have picked out some columns and you will see the formula that extracts the data

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ACCOUNTNUMBER</td><td style="font-weight: bold;color: #FFFF99;background-color: #808080;;">ZIP</td><td style="font-weight: bold;color: #FFFFFF;background-color: #808080;;">PICK UP LOCATION</td><td style="font-weight: bold;color: #FFFFFF;background-color: #808080;;">DELIVERY LOCATION</td><td style="font-weight: bold;color: #FFFFFF;background-color: #808080;;">DAILY COUNT</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #CCFFFF;;">66008140</td><td style="text-align: right;;">32570</td><td style=";">FL Dept of Health - 5527 Stewart St, Milton, FL</td><td style=";">Milton Patient Service - 5950 Berryhill Rd, Milton, FL</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A$2,Sheet1!$D$1:$N$4,4,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A$2,Sheet1!$D$1:$N$4,5,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A$2,Sheet1!$D$1:$N$4,9,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet1!$A$1:$D$4,MATCH(<font color="Red">Sheet2!A2,Sheet1!$D$2:$D$4,0</font>),1</font>)</td></tr></tbody></table></td></tr></table><br />

and the raw data is of course
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;color: #FFFFFF;background-color: #808080;;">DAILY COUNT</td><td style="font-weight: bold;color: #FFFF99;background-color: #808080;;">CALLER NAME</td><td style="font-weight: bold;color: #FFFF99;background-color: #808080;;">TIME OF CALL</td><td style="font-weight: bold;color: #FFFFFF;background-color: #000000;;">ACCOUNT NUMBER</td><td style="font-weight: bold;color: #FFFF99;background-color: #808080;;">GML REF #</td><td style="font-weight: bold;color: #FFFFFF;background-color: #808080;;">CLIENT ID/ NAME</td><td style="font-weight: bold;color: #FFFF99;background-color: #808080;;">ZIP</td><td style="font-weight: bold;color: #FFFFFF;background-color: #808080;;">PICK UP LOCATION</td><td style="font-weight: bold;color: #FFFF99;background-color: #808080;;">FSR NAME</td><td style="font-weight: bold;color: #FFFF99;background-color: #808080;;">TIME OF DISPATCH</td><td style="font-weight: bold;color: #FFFF99;background-color: #808080;;">CONFIRMATION FROM FSR</td><td style="font-weight: bold;color: #FFFFFF;background-color: #808080;;">DELIVERY LOCATION</td><td style="font-weight: bold;color: #FFFF99;background-color: #808080;;">PICK UP TIME</td><td style="font-weight: bold;color: #FFFF99;background-color: #808080;;">DELIVERY TIME</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #CCFFFF;;">1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #CCFFFF;;">10</td><td style="text-align: right;color: #333333;background-color: #CCFFFF;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #CCFFFF;;">33606</td><td style="background-color: #CCFFFF;;">HEART CARE CENTER 14320 BRUCE B DOWNS ** IN BILLING OFFICE</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="background-color: #CCFFFF;;">TPA LAB</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;background-color: #CCFFFF;;">1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #CCFFFF;;">66008140</td><td style="text-align: right;color: #333333;background-color: #CCFFFF;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #CCFFFF;;">32570</td><td style="background-color: #CCFFFF;;">FL Dept of Health - 5527 Stewart St, Milton, FL</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="background-color: #CCFFFF;;">Milton Patient Service - 5950 Berryhill Rd, Milton, FL</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;background-color: #CCFFFF;;">1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #CCFFFF;;">10</td><td style="text-align: right;color: #333333;background-color: #CCFFFF;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #CCFFFF;;">33606</td><td style="background-color: #CCFFFF;;">QUEST 4225 E FOWLER AVE TO: USF STUDENT HEALTH SERVICES 4107 CEDAR HILLS DRIVE STE 100</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="background-color: #CCFFFF;;">TGH</td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,364
Messages
5,528,265
Members
409,811
Latest member
pjwhyman

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top