Solved formula for vlookup needed

June 23, 2011 at 19:34:02
Specs: Windows XP
I need help to understand the vlookup formula for what I need to do. I have 2 workbooks.
I would like to match up a column of data in one workbook with item #'s in another.
First workbook column B has what I need for column B ( blank at this time) in workbook 2.
In column A of WB 1 has numbers that matchup to Column A in WB2. Not sure if that made sense... What I need is: If the numbers in WB1 and WB 2 Column A match up - then place WB1's Column B into WB2-column B

A (column) B (column)
Item # ID #

1223 2341


A (column) B (column)
item# ( needed if matches item # from WB1:B)

WB1 has 2600+ rows and WB2 has 4850 ( so half should be blank )

Thanks in advance

See More: formula for vlookup needed

Report •

June 24, 2011 at 11:01:05
Let's start by making sure we use the correct terminology.

This wording - column A of WB 1 - has no meaning.

A workbook does not have Columns. A workbook has worksheets (at least 1) and the worksheets have columns.

So my first question is: Do you have 2 workbooks or 2 worksheets?

The answer to this will make a difference in the way the VLOOKUP formula is written. If you have 2 workbooks, you'll need to include the workbook name and worksheet name in the formula (which Excel can do for you). If not, you'll just need the worksheet name.

That said...

As per the help files for VLOOKUP, the syntax is as follows:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: In your case, this would be the values in Column A of the worksheet where you want to populate Column B.

table_array: This will be the table, both columns A and B, of the worksheet that has the data you are "pulling from".

col_index_num: This is the column number of the table_array from which you want the data pulled once it finds the lookup_value in Column 1 of the table_array.

Keep in mind that the col_index_number is the column number of the table_array referenced in the formula, not the physical column number in the worksheet.

For example, if your table_array consisted of Columns A:D, a col_index_number of 3 would refer to Column C. However, if your table_array consisted of Columns F:H, a col_index number of 3 would refer to Column H.

[range_lookup] can be either TRUE (or 1), FALSE (or 0) or omitted, in which case it will default to TRUE.

Even though the [range_lookup] argument is optional with a default of TRUE, I recommend always using a [range_lookup] value to avoid any confusion. In most cases this argument will be FALSE (or 0) since in most cases we are trying to find an exact match. The TRUE (or 1) argument is certainly useful, it just doesn't get as much use in my experience.

So, generically, your formula might look like:

=VLOOKUP(A1, SHEET1!$A$1:$B$2600, 2, 0)

If placed in Sheet2, this will look up the value in Sheet2!A1 in Sheet1!A1:A2600 and return the value in Column B of the same row in which the value was found.

If you have values that aren't found, VLOOKUP will return #N/A. You can eliminate this by using an IF(ISNA( )) statement. This will produce a blank cell if the value in A1 is not found:


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

Report •

June 24, 2011 at 11:51:43
First of all. .....thank you!

Since this post....I have made a second sheet of the data I need to pull from.( instead of another file workbook.)

First sheet in the file is a column (A) of item id # and a blank column (B) next to it - labeled "catalogid"-

Column B is where I want the catalogid # to be matched up by item id#. via sheet 1-

on sheet 2 - is column A - which is populated with the "catalogid" and column B - which is the item id#.

what I need to accomplish is - by item id # - take the sheet -( which is sheet 2, column A) that has the data I need to match up by item id# on sheet 1.

Sheet 1 - Column A - item id # ( A1:4849)
Sheet 1 Column B - Blank ( need formula) labeled " catalogid"

Sheet 2 - Column A - "catalogid" (A1:2686)
Sheet 2 - Column B - item id # (B1:2686)

I only have about 1/2 of the catalogid# that will be imported. I expect with the correct formula to have #NA in approx. 1/2. of catalogid#'s that don't exist in sheet 2.

So, 2 sheets....both have item #'s that are the same , one with extra entries.
only 1 sheet has catalogid #'s with item id in same row.

So, what I need is: to match up item id# on both sheets and transfer the catalogid to the 1 sheet that corrasponds by item id #.

I know I complicated a simple problem....forgive me....

Thank you again,

Report •

June 24, 2011 at 19:07:19
✔ Best Answer
Once again, let's start by making sure we use the correct terminology.

Your A1:4849 should be written A1:A4849

All ranges need a Column Number and Row Number for the first cell and a Column Number and a Row Number for the last cell. A1:4849 doesn't mean anything.

OK, with that out of the way...

VLOOKUP can not look "left" it can only look "right".

Therefore, it would be much simpler if Sheet2 looked like this:

Sheet 2 - Column A - item id # (A1:A2686)
Sheet 2 - Column B - "catalogid" (B1:B2686)

An easy way to fix that is to right-click the B above Column B and choose Cut.
Then right-click the A above Column A and choose Insert Cut Cells.

That will "swap" the 2 columns.

Now, in Sheet1!B1 enter this formula:


Now, click in B1 and notice the small box in the lower right corner. Move your mouse over this box until the cursor turns into a black plus sign. Double click and it should auto-fill your formula down to B4849.

If you want blank cells where you have #N/A errors, use this:

=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$2686,2,0)), "", VLOOKUP(A1,Sheet2!$A$1:$B$2686,2,0))

or something like this

=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$2686,2,0)), "ID Not Found", VLOOKUP(A1,Sheet2!$A$1:$B$2686,2,0))

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

Report •

Related Solutions

June 25, 2011 at 06:36:36
Thanks you so much.....worked perfectly-

I will have to use formulas like this in the future a lot. Is there a book with simple instructions somewhere I can get - I hate to bug people.

once I have the formula I need - one or 2 of them - I will be set.

Again, Thank you Thank you

Report •

June 25, 2011 at 09:17:23
I'm sure there are books...I've never read any, so I can't offer any suggestions.

I'm all "trial and error" and "hang out in forums" taught.

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

Report •

June 25, 2011 at 14:02:24
I really can't say Thank You enough.......

Report •

June 25, 2011 at 16:45:47
I'm glad I could help and don't hesitate to come back the next time you have a question.

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

Report •

Ask Question