- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel Formula Question - Vlookup? Match?...other?
Posted on 5/1/15 at 10:46 pm
Posted on 5/1/15 at 10:46 pm
I need to insert names next to Social Security Numbers. I have a master list (#1) with all names and SSN and another list (#2) that has just 1 name but multiple social security numbers. The list is very long so copying and pasting is too cumberson. I am above ave with excel just have trouble writing certain formulas. Any help is appreciated.
2
Posted on 5/1/15 at 10:54 pm to Crow Pie
If you are simply trying to match the SSN from list #2 with the name from list #1, then vlookup is your answer. However, since your list has names on the left, you'll have to use Match and Index. Vlookup can only look right (or you can rearrange list #1).
I'm not at an excel computer so can't post syntax, but it should be pretty straightforward.
ETA: if you are formatted how list #2 looks, could be even simpler by just comparing SSN to the name and SSN above it using an IF.
I'm not at an excel computer so can't post syntax, but it should be pretty straightforward.
ETA: if you are formatted how list #2 looks, could be even simpler by just comparing SSN to the name and SSN above it using an IF.
This post was edited on 5/1/15 at 10:57 pm
Posted on 5/1/15 at 10:57 pm to LSUtigerME
I can move the columns around as needed if it will make the formula easier to write.
Posted on 5/2/15 at 7:51 am to Crow Pie
VLookup Function
If you switch SSNs to Col A in List 1 and Names to Col B.
In cell D4, type "=VLOOKUP(E4, A3:B12, 2, FALSE)"
This will search for the SSN in E4 in the table in A3:B12, and return the value on the same row in the 2nd column. "false" just forces it to find an exact match.
If you switch SSNs to Col A in List 1 and Names to Col B.
In cell D4, type "=VLOOKUP(E4, A3:B12, 2, FALSE)"
This will search for the SSN in E4 in the table in A3:B12, and return the value on the same row in the 2nd column. "false" just forces it to find an exact match.
Posted on 5/2/15 at 8:49 am to LSUtigerME
quote:
VLookup Function
If you switch SSNs to Col A in List 1 and Names to Col B.
In cell D4, type "=VLOOKUP(E4, A3:B12, 2, FALSE)"
This will search for the SSN in E4 in the table in A3:B12, and return the value on the same row in the 2nd column. "false" just forces it to find an exact match
Thanks! Yes, that works for cell D4...but when I copy the formula (using format painter) it moves the "A3:B12" parameters. How do I copy that formuala to look in the same area (A3:B12) over and over...the actual list (#2) I am working on is 2000 lines long and (list #1 is only 250 lines long)
This post was edited on 5/2/15 at 9:05 am
Posted on 5/2/15 at 9:36 am to Crow Pie
quote:
How do I copy that formuala to look in the same area (A3:B12) over and over
"=VLOOKUP(E4, $A$3:$B$12, 2, FALSE)"
You can then copy/paste down the column to your heart's content.
Edit: you only NEED the $ in front of the 3 and 12, but I always put them in front of both, in case I want to shift columns or expand the function to another column to pull another value with minimum effort.
This post was edited on 5/2/15 at 9:37 am
Posted on 5/2/15 at 10:11 am to BamaAtl
Thanks this will save me much time each month as I create list of Safety Certification that will soon expire for a large team of refinary/oli gas workers.
Posted on 5/5/15 at 6:40 am to BamaAtl
What does the $ represent in excel formulas?
Posted on 5/5/15 at 7:36 am to CHiPs25
It locks the formula to that cell. You can highlight the cell or range in the formula and hit F4. If you keep hitting F4 it will change to locking just the row or just the column etc. it'll also clear the $s
Posted on 5/5/15 at 8:37 am to CHiPs25
quote:
What does the $ represent in excel formulas?
The long answer is that it changes the reference in a formula from relative to absolute. If you have a formula in cell B2 that references cell A2, Excel interprets that as "the cell to the left of this cell." When that formula is copied to cell C2, "the cell to the left of this cell" is now B2, not A2. If you want to keep the formula referring to cell A2, you would put $A2 in B2. Then when copied to C2, it would stay $A2.
When you copy $A2 from B2 to B3 it will become $A3. If you do not want the row reference to change, then you would need $A$2.
$C$R - Absolute column, absolute row
$CR - Absolute column, relative row
C$R - Relative column, absolute row
CR - Relative column, relative row
Posted on 5/5/15 at 9:48 am to CubsFanBudMan
Yeah just think of $ as locking mechanisms.
Posted on 5/6/15 at 3:23 pm to CubsFanBudMan
quote:
CubsFanBudMan
Awesome explanation, thanks a lot.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News