» Vlookup Formula – Organizing the Data Table

Is there anyway when doing a Vlookup, that if the Vlookup value doesn't exist I can somehow stop the value reading #N/A. Can I make it be 0 or blank ?

Read the tip:
How to change error result calculation when using Vlookup formula to 0 or empty cell in Microsoft Excel

Where do I find this tip to change the error result calculation in a vlookup so it doesn't read #N/A ?

copy the tip titles words into the search box at the upper left side and press on "search site"

Use the IF function.

The logical test is

ISERROR(VLOOKUP("whatever")).

If the vlookup produces an error, have the IF function produce a zero or blank cell.

If the vlookup doesn't produce an error, have the IF function execute the vlookup.

Here's a sample of the complete IF statement, producing a blank cell in the event of an error:

=IF(ISERROR(VLOOKUP("whatever")),"",VLOOKUP("whatever"))

Substituing a set of valid VLOOKUP arguments for the "whatever"

Can we use an "If" function within a "vlookup" function to select the result to be displayed?

If I understand your question correctly, I think you would not use the if function in vlookup, but rather the other way around. (Vlookup in If):

IF(Vlookup(item,range,columns,0)=ABC,do something,do something else)

If your vlookup finds the data you want (ABC), you can then instruct excel to execute some function or return a particular value. If it does not find ABC, instruct it to do something else.

In some way I have problems using VLOOKUP when I need to specify a column number from a differnt worksheet. How do I specify a different worksheet/column number? Do I have to specifiy the other worksheet once again after doing so in the "range"? What´s wrong...

is there a way for vlookup to read down the list of numbers and if your number is not the exact match, return the larger of the two numbers that the number falls between?

I have a list of companies, like 8000 companies and i need to eliminate the companies which have the words electric or medical or mesh in their titles how do i do that? E.g excel should go through my list of companies and say ok ABC Electronic Reader -> delete.

Hi,

I'm trying to change a column of dates in MMDDYR (Year is only two digits) into a column with the year as four digits, any suggestions, PLEASE.

i have an assessment task due soon for my school work, and i cant ge it completed. i need to use the VLOOKUP function to allocate points to cells. i have a reference table made already, determinng the points needing to be allocated, but i cant get it to work.. someone please help me.. im desperate

I have an assignment to distinguish between FIXED and SLIDING scales, describe how they function, which is better and risks of both. PLEASE LET ME KNOW OF ANY INFO ABOUT THEM,AS I AM BATTLING TO LOCATE ANY!! AND AM TIGHT 4 TIME.

Hi Mike,

That is exactly the kind of behaviour that Excel's VLOOKUP function will exhibit unless you eplicitly tell it not to.

Have you read through the Excel help topic in detail? If so, and you need further help, post back with a detailed example of what you want to do, and I'm sure we can help.

Alan

how do i return something different i.e 0 in a vlookup formula rather than N/A when it cannot find a match

Hi Geoff,

One way is to use an error trapping formula.

For example, if A1 contains your VLOOKUP

A2 could contain:

=IF(ISNA(A1),0,A1)

That will giv zero if the VLOOKUP returns N/A.

You could also replace both A1s in the formula above with the actual vlookup, but that would get both messy and rather long perhaps.

Hope that helps,

Alan.

i placed my query here the other day, and am desperate for replies! Alan???!!! Please, regarding FIXED and SLIDING scales with VLOOKUP. thanks

Hi Bridget,

I have to admit that I don't really understand your question.

Are there no boundaries to the scope of your assignment / studies?

Sorry if I am being dim!

Alan

I managed to work out what the 'scales' are, they are not types of 'functions' just different ways of working with the VLOOKUP function - thus I was unable to get any info relating t them. But managed to figure them out by using examples etc. Thanks though.
Bridget

Hey,

I'm trying to use the VLOOKUP and LOOKUP function to ref. a value on another sheet. I'll telling it a name ref'd in my output table and having it lookup a value in my reference table. It is working except for the fact that it is returning the value in the row above the actual value. Example:

Name/Age
Bob Smith/29
Jim Peson/21
Tom Gross/35

I want it to seach for "Tom Gross" and return his age (35) but its searching for "Tom Gross" and returns "21" instead (the value in the row above it). What's going on? I've tied LOOKUP and VLOOKUP and they both do the same thing

Hi Greg,

Difficult to say why it isn't working since you haven't given the formula you are using.

However, VLOOKUP should be fine.

If the table is in A1:B4 (title row included), then:

=VLOOKUP("Tom Gross",A1:B4,2) {=35}

Does that not work for you?

Alan.

I need help i have been working on a multipage spread sheet. I want to enter a product number into B18 B19 etc. and then i want it to look through page KS1 column A to find the matching product number and to bring over the name of the product from Column B. Here is the formula i used.
=IF((B18>0),LOOKUP(B18,KS1!A1:B4000),0)
The problem is that it brings the wrong product to the page. I have tried a few formulas but they all do the same thing. Please help

Hi Holly,

Try this:

=IF(B18>0,VLOOKUP(B18,KS1!A1:B4000,2,FALSE),0)

I suggest you put the FALSE on the end, since a 'close enough' lookup for product numbers is probably not a good idea and may be the cause of your problem.

Does that work?

Alan.

Alan thanks for the help. That formula seems to work better then mine but it shows NA for the description of the products when i get to the product number on row 100 and beyond.

I spaced the first hundred double space and then i started single space i don't know if that has anything to do with it. That is the only difference i found. Also there are 24033 rows so i dont' know if there is a limit?
If you have any more suggestions PLEASE :) let me know.

Holly

Something i have noticed is that the first 100 entries have darker lines outlining them. Like a border. The items on the bottom of the list are a different font and don't have the darker lines. Maybe that is something effecting the outcome???

Hi Holly,

The formatting should have absolutely no impact on the results so we can eliminate that as a possible issue.

In your formula, did you make the lookup destination range absolute:

KS1!$A$1:$B$4000

If not, then when you copied down, it probably left your destination data out of sync, and hence could not find some codes that should be there.

Does that fix it?

Alan.

Thanks,

It's working correctly now. The problem was the names in the reference table weren't exactly the same (2 spaces at the end) as the names beening searched. Once I fixed that it worked like a charm.

Allan
It is still working until i get to Product 100. I had cut and pasted from row 100-24333 so it is in a different format i think. Maybe that is the problem? But thanks for the formula before it wasn't even doing the first 100.

holly

Is there any way to do a lookup and pull multiple rows of information based on the same lookup value?

For example I have a monthly sales spreadsheet by customer number and I want to pull a specific customers information to another spreadsheet. This customer has multiple purchases during the month.

Is there anyway to do this?

It finally worked. Thanks Alan for all of your help
HOLLY

I am trying to lookup values from multiple pages of my spreadsheet and I cant figure out how to write the formula.
=IF(B18>0,VLOOKUP(B18,KS1!A:B,2,FALSE),0)

ks1 is the first page and i want to look at page Lf1 and lf2 etc. the format is the same on all pages just different brands of products.

Hi Christine,

I think fro your description, that you should look at using a pivot table.

They are very useful for summarising a dataset into a logical layout, and are extremely flexible.

Have a look, and post back if you need more help.

Alan.

Hi Holly,

Glad to see that it worked above.

As you noted, it is very important to be aware of the actual structure and form of the data that you are analysing. Those leading and trailing spaces make a big difference to a computer!

Regarding lookups across different sheets:

I normally suggest to people that they create a new sheet, and popoluate that with separate lookups (perhaps one column per data sheet) that effectively summarises the larger dataset.

That way, the formulae in the new sheet are all relatively simple (normal lookups on single sheets).

Then you can create a single (simple) lookup on your final output sheet that references just the single intermediate sheet.

It also has the benefit that if you or someone else is trying to understand it in three months time, you will have some hope of seeing what is going on!

HTH,

Alan.

Can you have nested Vlookups where the inside function is returning a Named range for the outside function to use? Excel returns #NA.

Hi Gary,

You can certainly nest VLOOKUPs although as per my reply above, I suggest simplicity for each cell is also important, so you might be better splitting them out in a calculation zone.

However, aside from that, you talk about a VLOOKUP returning a range for another VLOOKUP to use.

The normal output from a VLOOKUP would be a value derived from a cell in the destination lookup table.

I cannot quite see where you are going if you are looking for VLOOKUP to return a range?

Alan.

I am developing an input sheet to define which external data table should be used in a subsequent lookup routine. Can the ultimate range name be constructed by concatenating the result values from independant Vlookups? Informed responses with email addresses are appreciated.

Hi Gary,

You can construct the lookup range from parts as for any other formula using the normal INDIRECT function, which may utilise the results of other formulae including VLOOKUPs.

If you post a more specific example, I'm sure someone will offer you an 'informed response' ;-)

On a separate note, I would encourage replies to be posted here rather than via email for two reasons:

1) Everyone else benefits from the discourse, and the ExcelTip community can learn from each other; and

2) If you post your email address anywhere on the internet, you may as well wave a big red flag saying, "Please spam me!". Personal choice of course.

Hope that helps,

Alan.

Alan...Thanks. I appreciate your insight and guidance.

I have a vertical column (A) of reference text data in Excel 97.

I have a separate set of 5 columns of data (B,C,D,E,F) within the same worksheet, and only column F is known to possibly contain the reference data from various cells in Column A.

I want to search each of the cells of Column A text data on a one by one basis, and then search for each of those pieces of data within Column F. If I find the data in say row 25 of Column F, then I want to change the format of the found data by carrying out a “strikethrough” and “colouring” of the text contained within the same line i.e. B25, C25,D25,E25,F25.

I have found no examples of conditional formatting and Vlookup. I have also experimented with VLookup but find that the formula seems to want a particular data “value” rather than what data exists within the “referred to” cell.

Can anyone guide me on this please?

Hi Noel,

If your data looks like this (A1:E3):

Alan 20 75 87 Eric
Bob 79 64 88 Doris
Charles 12 10 13 Alan

Then you could add another column on the right (or indeed anywhere else you like, but I would put it to the right), containing the following formula in F1:

=COUNTIF(A$1:A$3,E1)

Obviously you will need to adjust for the actual number of rows and columns you have.

This will generate a zero if the item in Row E is NOT contained in Column A, else you will get a number greater than zero (if Column A contains only unique entries, you will not get anything more than 1).

You can then use the results in that column to conditionally format the other cells (B:E - or whatever).

Hope that helps,

Alan.

Alan
Thank you for your quick response.
The data is UNIQUE.
Using your example (table below) and your suggested solution, when I search B1:E7 for 'Charles' and “Tom” (found in Column A) in the following table ...

Charles Alan 20 75 87
Tom Bob 79 64 88
Charles 12 10 13
Bill 45 33 21
Joan 34 22 10
Noel 56 44 32
Tom 12 1 22

it changes to:-

Charles Alan 20 75 87 1
Tom Bob 79 64 88 1
Charles 12 10 13
Bill 45 33 21
Joan 34 22 10
Noel 56 44 32
Tom 12 1 22

The '1' values from the countif condition are located in F1 and F2 and because I have two '1' s now, the data is no longer unique. These are on different lines to the data that needs to be struckthrough i.e. B3:E3 and B7:E7.
I cannot see how to connect the NEW values in F1 and F2 to the rows B3:E3 and B7:E7 for a conditional strikethrough. I am only seeing the same problem of “If Charles in A1, Then Search B1:E7 for Charles, and If Found, then Strikethrough the line found e.g. B3:E3.

Sorry if I have missed something obvious here.

Hi Noel,

If you now go to cell B1 select:

Format
Conditional Formatting
Change the drop down to "Formula Is"
In the formula box, type the following *without* the quotes: "=$F1=1"

Note that the column (F) is fixed, but the row is relative.

Select the formatting that you want to apply
Click Ok to get back to the worksheet.

If F1 actually is evaluating to '1' then the formatting should now be applied to B1, else nothing will appear to have changed.

Use the format painter icon to copy that conditoinal formatting from B1 to B1:E999 (or whatever the bottom row of your data / formulae is).

That should do it!

Alan.

Hi,

In one worksheet I'm systems that I'm trying to track.
but I'm missing the data in another field. I have that
data in another worksheet.

worksheet one: fields in Colum B contain my system names. but column c is missing the data. (lots of it.)

worksheet two: field in Colunmn N contains also my
system names but colum I contains the data I need to copy into worksheet one matching exactly those systems.

Now with that in mind what should the syntax be for Match or VLOOKUP to accomplish my task?
VLOOKUP

Hi Phillip,

First, to make it easier, can you copy Sheet2!I:I to, say, Sheet2!O:O (or any other column to the right of N)?

If so:

If the data in Sheet2!N:N contains only unique entries, then you could use the following formula in Sheet1!C1:

=VLOOKUP(B1,Sheet2!N:O,2,FALSE)

Once the data is in Sheet1!C:C you can use copy / paste as values to fix it, and then delete the surplus column from Sheet2 that you copied over initially.

Does that work for you?

Alan.

Dear Alan

I have succeeded in effecting a conditional format based on the value in column F i.e. ‘1’ values. The $F1 value is looked at and if ‘1’ then the format is applied to B1:E1. However, I forgot to mention that the number of data in column A does not equal the number of data in column B. i.e. I might have 1000 items in column B, and 100 in column A. And so again ~

I want to cross off the 100 items in column B that are originally in column A. But, the 100 items from Column A are NOT necessarily on the same line in column B as in the solution that you have proposed. I think the solution that you have put forward assumes that the data is on the same line and thus I think that the strikethrough is occurring on the same line whenever it sees a NON UNIQUE value of ‘1’ which was established from the countif earlier. I should also mention that my code so far goes as follows:-

In cell F1
=COUNTIF(B$1:B$6646,A1)
If A1 data is found at B123, then F1 converts to 1
The next formula is in B1 and as you suggested is
=$F1=1
The conditional format strikethrough then results in line 1 even though the data was needed to be struckthrough on line B123:E123.

Therefore, I think we are almost there but not quite.

Thank you for your patience with this one. Regards…..Noel

Hi Noel,

No probs - happ to help.

I think we are, as you say, 'almost there'.

If you have your data as follows (from an example up above):

Charles Alan 20 75 87
Tom Bob 79 64 88
Charles 12 10 13
Bill 45 33 21
Joan 34 22 10
Noel 56 44 32
Tom 12 1 22

This covering the area (A1:E7) but with nothing in A3:A7.

If you put this formula in F1 and copy down:

=COUNTIF(A$1:A$2,B1)

This will place a '1' beside rows 3 and 7 (Charles and Tom in column B).

You can now apply the conditional format as discussed above, based on the result in column F.

Note that even if column A contain some non unique entries (two entries for 'Tom' say), then you can still use thi method, except that some of those '1''s in column F will become '2' or more.

In that case, just use a conditional format that looks for a number greater than zero (not just '1').

Does that do it?

Alan.

Alan

This has solved the problem. Thank you for your advice - it is much appreciated.

Noel

I am trying to compare some data on two different sheets and then return the value of a different column if they match. Here is my formula.

=VLOOKUP(B2,'Our Status'!$A$1:$A$2123,2,FALSE)

When I gave the column index set to 1 it returns the compared value or #N/A if not matched. If I change it to 2 I still get the #N/A's but when it is supposed to return for a matched value all I get is #REF. Any help is appreciated.

Hi Scott,

Your formula contains the range (A1:A2123) which is a one dimensional 'list'.

However, you are asking VLOOKUP to return the value from the second column of that range (which does not exist).

If you change your formula to include column B, it should work:

=VLOOKUP(B2,'Our Status'!$A$1:$B$2123,2,FALSE)

HTH,

Alan.

That worked Alan. Thanks for the help! :D

is it possible to use vlookup on more than 1 value
eg a table with times in column a, names in column b and values in column c.
so i would want to check values in columns a and b and return values in column c where the 2 values match. i know it can be done with a pivot table, but i want the spreadsheet to be as automatic as possible, ie no need to refresh a pivot table.

any ideas??

I am comparing to lists using VLOOKUP. My task is to get phone numbers from the master list, (50,000 names, addresses and phone numbers) transposed to my list, (100 names and addresses). VLOOKUP finds the common name. I need to know how to tell it to "grab" the adjacent phone number as the response to the argument answer of "TRUE", and enter in the cell where the formula is. Hope this is clear, cause I'm scratching my head. Thanx in advance.

Hi Phil,

Suppose you have the following data in (A1:B4):

Apples       Red     345
Bananas    Blue    43556
Bananas    Red     34
Apples       Blue    234

Then the following formula will 'pick' the value '234' from the data:

{=INDEX(C1:C4,MATCH(1,(((A1:A4)="Apples")*((B1:B4)="Blue")),0))}

Note that this is an array formula - check the online help for more on arrays.

Alan.

Sorry - obviously that data is in (A1:C4).

Alan.

Hi Charles,

That sounds exactly what VLOOKUP will do for you.

Have a look at the online help, and post back with a specific example (small data set and your formula) if you can't get it to work.

Alan.

hi alan,
thanks for the speedy reply, could you explain the structure of the formula so i can amend it to my spreadsheet?

Hi Phil,

VLOOKUP:

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

Syntax

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.


You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.


The values in the first column of table_array can be text, numbers, or logical values.


Uppercase and lowercase text are equivalent.
Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.


If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.


If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

Do you have any specific problems getting it to work?

Alan.

Hi Alan,

I have a problem with my Vlookup when the data in the table is not in ascending order. Do I need to have a macro that sorts the data, any tips?

Thanks,

James.

Hi James,

If you are using the FALSE parameter, then it won't matter unless you have multiple results for a given search value (in which case you'll only get the first one it comes to).

If you are using the TRUE parameter, then you definately have to have the data sorted.

HTH,

Alan.

Hello Alan.

This is probably considered a dumb question, but heres my situation.

I have a number (i.e. 350,000) that looks up the number on a separate sheet to give me a value in the same row, different column.

What it currently does is just look up the first 3 digits (i.e. 350,XXX) and gives me the number.

What I need it to do now is round the number to the nearest thousand before it looks up, but not change the original number. Nearest thousand would be 499 and below and; 500 and above.

Please help.

-Jon

Hi Jonathan,

If you want to round a number in A1 to the nearest thousand, just use:

=ROUND(A1,-3)

Does that solve your problem?

Alan.

Hi Jonathan,

I'm not sure why it doesn't work for you.

After you applied the above answer, did your formula look something like this:

=LOOKUP(Round('Sheet 1'!A1,-3),'Sheet 3'!A1:A1000,'Sheet 3'!B1:B1000)

Basically, you are taking the value in Sheet1!A1, rounding to the nearest thousand, then looking for that (rounded) value in your lookup range (Sheet3!A1:A1000), returning the corresponding value in Sheet3!B1:B1000.

Not sure what the range after the plus sign is doing there, but that is a different issue.

How does it not work exactly? What results are you getting that is not what you need?

Thanks,

Alan.

No, it doesn't.

I'm using the original number (i.e. 350,565) to add to other numbers on a separate sheet....

Maybe if I put my question like this, you'll understand better, using this formula:

=LOOKUP('Sheet 1'!A1,'Sheet 3'!A1:A1000,'Sheet 3'!B1:B1000+'Sheet 3'!D1:D1000)

Instead, I need 'Sheet 1' A1 to stay 350,565 but to find the 351,000 reference in Sheet 3 for the correct referece amount..

If the original number was 350,325 then I would need the 350,000 reference. Rounding to the nearest thousandths, I think...

Does that help?

Jon

Thanks man...

I wasn't putting in the -3... whatever that is for... it works now..

If you weren't a dude, I'd kiss you.

Jon

how do I vlookup 2 datas simultaneously?
For Eg. Main heading has the date and subheading are agent codes.Date column is merged with the agent names below

like Sep-1 Sep-2
A B C D A B C D
Area1
Area2

And in another sheet I use vlookup formula or any other excel formula so that I enter the date in a cell and the agent name I should get the numbers of the agent.

Hi Iqbal,

As a general comment I suggest you read up on the MATCH and INDEX functions - they look likely to solve your problem.

more specifically, what data are you looking to return from the table you gave? You mention returning 'numbers of the agent', but your table layout contains no numbers?

Alan.

Could someon explain how Match and Index could solve the problem of multiple matches? I haven't been able to figure it out.

I think I'm having the same problem as Iqbal- I'm trying to match up products that sometimes compare to more than one competitor product. I'd like Vlookup to take out all the matches, not just the first one it finds. Any help would be greatly appreciated.

dan