Solved Excel VLookup Function Help

March 2, 2016 at 10:35:32
Specs: Windows 7
I really really need help:

I have data in column A with call it Names. I need to first check which Names are in or not in Column B in a new worksheet. If one of those data cell names are not in Column B I need to add it to column B along with the corresponding data in columns C,D,E,F,G onto the main sheet where column B is.

So I am checking if data exists, if it does not exist I am copying over the corresponding data into data columns that already exists which are columns C,D,E,F,G.



See More: Excel VLookup Function Help

Report •


#1
March 2, 2016 at 16:39:08
This does not sound like something VLOOKUP can do. VLOOKUP needs to find data in order to extract data from other columns. If the data doesn't exist, it can't "create" it.

It sounds to me like you need a macro that compares each cell in SheetX!Column A to the data in SheetY!Column B and creates an entry in SheetY for each piece of data that it does not find.

Before I offer any code, I need to know if that is correct.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
March 4, 2016 at 05:53:59
✔ Best Answer
I would like to amend my previous response.

You could use VLOOKUP to accomplish your goal, but it would be messy.

If you place this formula at the bottom of your Column B list and drag it down as many cells as you have values in your Column A list of names, it will pull over any name that does not exist. However, it will leave blank cells for any name that already exists in Column B.

Note: $B$1:$B$4 will need to be modified to match your specific range.

=IF(ISNA(VLOOKUP(Sheet1!A1,$B$1:$B$4,1,0)),Sheet1!A1,"")

In other words, let's say you start with this in SheetX!Column A...

           A
1         Tom
2         Barb
3         Ann
4         Bill
5         Ed
6         Sue
7         Len   

...and this in SheetY!Column B:

           B
1         Tom
2         Barb
3         Bill
4         Ed 
5         =IF(ISNA(VLOOKUP(Sheet1!A1,$B$1:$B$4,1,0)),Sheet1!A1,"")
6        (Drag B5 down 7 cells to match the length of SheetX!Column A)

You will end up with this:

           B
1         Tom
2         Barb
3         Bill
4         Ed
5
6        
7         Ann
8
9
10        Sue
11        Len

Obviously you'll need formulas in SheetY!C:G to pull in the rest of the data.

Once all of the data is in SheetY, you could clean it up by doing a Copy...PasteSpecial...Values and then delete the blank Rows using whatever method you prefer (sorting, deleting, filtering, etc.)

I hope that helps.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
March 7, 2016 at 11:30:37
This is exactly what I did, it worked, thank you so much!

Report •

Related Solutions

#4
March 7, 2016 at 12:07:19
I'm glad to see it worked out for you.

If this was something that you need to do quite often, I would still suggest using VBA. Once the code is written, a single click of the mouse would complete the entire task.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question