Email:      Pass:    Pass?

Subscribe for Free Excel tips & more!
E-mail:
Advertisements

# Vlookup, Return Zero If Not Found

I have a formula where I use multiple lookups to return several values, which
I add together. e.g =vlookup(A) + vlookup(B) +vlookup (C)

If vlookup A = 1, B = 3, & C = 2, everything is good and the result = 7.

But in some cases the value one of them is looking up does not exist in the
array.
If this happens it causes the whole formula to return #N/A. I would like it
to give the one that has the #N/A to return the value of zero. So say
vlookup A = #N/A, my total formula would = 6

Any help appreciated. Thanks.

## Similar Excel Tutorials

How to use the Vlookup Function in Excel
Full explanation of the Vlookup function in Excel, what it is, how to use it, and when you should use it. The Vlook ...
Vlookup with a Range of Numbers in Excel
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that y ...

## Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Output the Worksheet Name in a Cell in Excel - UDF
- UDF to return the name of the worksheet in Excel on which this function is located. There are no arguments for this fun

## Similar Topics

Hello,

I'm currently using a VLOOKUP to return values that correspond to a unique ID#. However, that ID# can apply to more than one object, and therefore the VLOOKUP must return all the corresponding values, not just the first it finds. I can't get a successful VLOOKUP for this though.

Please see the attached file with the VLOOKUPs I'm currently using.

Any help would be greatly appreciated!

-Jackie

Hi all,

I have this array formula which I will be using to sum an overall total:

Code:

=SUM(IF(ISTEXT(P8:BX182),(LEFT(P8:BX182,1)="H")*(0&TRIM(MID(P8:BX182,3,255)))))

What I would also like to do is run a VLOOKUP formula in another sheet (lets call it sheet2) where values in column A (name) are the reference for the VLOOKUP. I then just want to return a result for the row starting with that name in sheet1.

Frankly VLOOKUP confuses me a little, and without the help of the mighty Ron the above formula wouldn't even exist!

Any help greatfully received!

How do I write a vlookup so that instead of returning a single value, I return the sum of an array that starts with the returned cell?

So, if my vlookup is this:
=vlookup(A1,Table,2,false)
and it returns 100.

Let's say the array from which the returned value looks like this:

50
100
150
200
250
300...

However, instead of 100, i want the vlookup to return the sum of the returned cell + the 2 cells below it. So, my new vlookup formula would return 100+150+200 = 450.
I don't want a string of 3 vlookups because conceivably, my returned array can be 12 cells long and dynamic as well (i.e., won't be 2 cells all the time). Thanks.

mach3

Hi, i was wondering if there is a way to make an or formula to return the value instead of the true statemnet

In any case here is the formula i used, it works properly but i need to get the value instead of the "TRUE" statement

Any one has any idea: Thanks in advance

=OR(IF(\$D\$2="Low",IF(\$D\$3="ALL",VLOOKUP(\$B7,sav,3,0)*HLOOKUP(\$D\$1,geo,4,0),IF(\$D\$3="NA",VLOOKUP(\$B7,sav,3,0)*HLOOKUP(\$D\$1,geo,2,0),VLOOKUP(\$B7,sav,3,0)*HLOOKUP(\$D\$1,geo,3,0))),0),IF(\$D\$2="Aggressive",IF(\$D\$3="ALL",VLOOKUP(\$B7,sav,4,0)*HLOOKUP(\$D\$1,geo,4,0),IF(\$D\$3="NA",VLOOKUP(\$B7,sav,4,0)*HLOOKUP(\$D\$1,geo,2,0),VLOOKUP(\$B7,sav,4,0)*HLOOKUP(\$D\$1,geo,3,0))),0),IF(\$D\$2="Expected",IF(\$D\$3="ALL",VLOOKUP(\$B7,sav,5,0)*HLOOKUP(\$D\$1,geo,4,0),IF(\$D\$3="NA",VLOOKUP(\$B7,sav,5,0)*HLOOKUP(\$D\$1,geo,2,0),VLOOKUP(\$B7,sav,5,0)*HLOOKUP(\$D\$1,geo,3,0))),0))

each of the 3 parts of the or il will return a value if used individually.

Probably not possible, but I want use a Vlookup to return a formula as a the value for the Vlookup result.

formula = vlookup(s9,rebar,3,false) result = an equation 12xa1xb2

Is this possible, the reason I need this is because defferent cells will have different calculations based on Cell A1.

Thanks,

Hey guys,
I am using VLOOKUP (false) and when it can't find the value that I am looking for it is returning #N/A. I want it to return a 0 (zero) instead. Is there a way to do this?

I have attached screenshots of what I mean. So in cell B6 i have used the formula: =VLOOKUP(A6,L3:O42,2,FALSE) and it returns a #N/A because there is no "BXB" in the array that I have selected. I essentially want it to return a 0. Not sure if there is another function that will do it or I can fiddle with the VLOOKUP one!

Capture.JPGCapture2.JPG

Thanks!!

I am using a Vlookup that does tno always return a value because there is nothing to return. THerefore I get a #N/A. However I would like it to return a 0 if there is nothing to return. My Vlookup looks like this :
=VLOOKUP(C:C,'SIOP PRODUCTION'!A:D,4,FALSE)

I know that I amust havea case of cerebral flatulence, becuase I know that I have done this before.. Can anyone help?

Jon

Hi, I hope someone out there can help me with this as I have spent 2 days trying to work it out and am getting nowhere!

I need a formula that will do as a VLOOKUP does with returning a lookup value, but I need it for multiple values and VLOOKUP will only return the first value.

I though I had got somewhere with an INDEX formula but this is not working, I've attached a sample file - if anyone could help me with this it would be much appreciated.

Thanks

Nikki

Vlookup [=VLOOKUP(\$C\$24,\$A\$1:\$B\$21,1,FALSE)]
fails when the value you are looking up is > 0.65 even though the value is clearly in the array. 0 through .65 return correct result, .70 returns NA or .65 depending on whether you use the range lookup.

When I just use whole numbers, all is well.
I have made sure all cells are formated as numbers.
The array is obviously sorted correctly.
I have tried ROUNDing to no avail.
Any other tricks to try?

fractional values work from 0-.65 then fail at .70
0.00 1 0.05 2 0.10 3 0.15 4 0.20 5 0.25 6 0.30 7 0.35 8 0.40 9 0.45 10 0.50 11 0.55 12 0.60 13 0.65 14 0.70 15 0.75 16 0.80 17 0.85 18 0.90 19 0.95 20 1.00 21 Value to look up that works 0.65 good vlookup result --> 0.65 Value to look up that does not work 0.70 bad vlookup result --> #N/A

whole numbers work
0.00 1 5.00 2 10.00 3 15.00 4 20.00 5 25.00 6 30.00 7 35.00 8 40.00 9 45.00 10 50.00 11 55.00 12 60.00 13 65.00 14 70.00 15 75.00 16 80.00 17 85.00 18 90.00 19 95.00 20 100.00 21 Value works 65.00 good vlookup result --> 65.00 Value also works 70.00 second vlookup result --> 70.00

I have a nested series of about 7 different lookup tables that work like this:

=IF(ISNA(VLOOKUP(1ST ARRAY)),IF(ISNA(VLOOKUP(2ND ARRAY)),IF(ISNA(VLOOKUP(3RD ARRAY)),IF(ISNA(VLOOKUP(4TH ARRAY)),IF(ISNA(VLOOKUP(5TH ARRAY)),IF(ISNA(VLOOKUP(6TH ARRAY)),VLOOKUP(7TH ARRAY),VLOOKUP(6TH ARRAY)),VLOOKUP(5TH ARRAY)),VLOOKUP(4TH ARRAY)),VLOOKUP(3RD ARRAY)),VLOOKUP(2ND ARRAY)),VLOOKUP(1ST ARRAY)).

I changed the actual lookups to "# ARRAY" for simplicity's sake. The formula itself does what I need it to do, but I am wondering if there is a way for conditional formatting to indicate which array the result is being taken from. For example, if the result is being taken from the 1st array, color it blue. If it's being taken from the 2nd array, color it red, 3rd array, green, and so on and so forth. Can this be done?

Hi!

I want to use Vlookup, or any excel function or macro, that would enable me to look up a value in an array and return the value in column 2. (Now this is easy with VLOOKUP) But Vlookup stops at the first match. I want to return all values that correspond to the lookup value. For example,

Col A Col B
XXX Y12
XXX Y22
XXX Y32

I would like excel to return Y12 in a column, Y22 in the next column and Y32 in the following column if possible.

Please advise!

~Renita

I am trying to do the following and getting caught up on the final part of the formula. I would like to run a Vlookup that checks for three individual things. For instance, the table of data can have three results, either two specific words or a number.

So far I have gotten the formula to work with one of the words/text results but can't get the second.

My goal is the following:
Perform Vlookup, if the vlookup results in a numeric answer use that number, if the result of the Vlookup is "YES" return the a predetermined result in cell E3, and if the result of the Vlookup is "No" return the predetermined result in cell E3.

current formula that doesn't have the "No" component. =IF(VLOOKUP(A3,Data!A:B,2,FALSE)="Yes",Positions!\$E\$3,VLOOKUP(Positions!A3,Data!A:B,2,FALSE))

Dear All,

Please can you offer me some advice.

Say I wanted to lookup value A5, and return all columns in the range A1:D5, I could use the formula =vlookup(A5,\$A\$1:\$D\$5,2,false) and copy this formula 3 times changing the third parameter 2, to 3 , 4 and 5. i.e 4 VLOOKUP formulas.

I could, instead, convert the VLOOKUP into an array and enter it CSE i.e =vlookup(A5,\$A\$1:\$D\$5,{2,3,4,5},false). This returns the right answer but "appears" to have only used 1 VLOOKUP.

What if this was extended to say 26 columns so instead of "hard coding" the array constants I used =vlookup(A5,\$A\$1:\$D\$5,COLUMN(B:AA),false). Is this better than 26 indivisual VLOOKUP's in term of speed?

Many thanks in anticipation,

Pete

Hello;
I have viewed numerous threads regarding: Vlookup returning a blank cell if is lookup returns #NA.

Yet, when I use the the accepted formula format given on many different websites I get a return of a number zero when I really want a return of a blank cell. Here is my original Vlookup formula:
=VLOOKUP(A300,'VLOOKUP_FOR_BLANK_RANTALA_Request For SAP Upload Form.xlsx'!LookupContainer,12,FALSE)

Here is the formula I had hoped would return a blank cell:
=IF(ISNA(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)),"",(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2))

Any help would be very appreciated. Thank you in advance
dh

I am sure this has been posted before, but I've been looking for the answer in past posts for a while now and am getting impatient. I want to do a vlookup to return the sum of columns 2, 3 and 4. I know this can be done with:

=sum(vlookup(\$a\$1,array,2)+vlookup(\$a\$1,array,3)+vlookup(\$a\$1,array,4))

However, I think there is a faster, more efficient way as I will have to do this same thing again, but with many more columns to sum.

Thanks

Hello all,

Using Excel 2003.

I want a vlookup to return a value of "1" if the vlookup does find the value. If it doesn't find the value then it would return ".40".

Board member VoG sent me formula below but it only returns a value of "1" even if it matches or not.

=IF(ISNA(VLOOKUP(B89,Sheet3!\$G\$4:\$G\$89,1,FALSE)),"1",".40")

Any help would be appreciated.

Thanks,
Eric

i have a system that returns 0, 1 or #N/A in a range of cell say B4 to B7. i want to return the value on column C corresponding to the row where the value 1 is found. for example b4=0, b5=1 b6=#n/A, C4 26, C5=366 and C6 #N/A. i want the formula to return 366. here is what i have tried IF(ISNA(VLOOKUP(1,B4:C6,3),0)) IT didnt work. when i tried vlookup(1,B4:C6,3) i got #REF! error. please help

Hello, people.

I have an Excel P&L statement that uses VLOOKUP formulas to return individual accounts within a category, and a SUM formula to total each category. Example:

=VLOOKUP(\$F73,RSLookupList,6,FALSE)
=VLOOKUP(\$F74,CampLookupList,6,FALSE)
=VLOOKUP(\$F75,CELookupList,6,FALSE)
=VLOOKUP(\$F76,ECCLookupList,6,FALSE)
=VLOOKUP(\$F77,RSLookupList,6,FALSE)
=ROUND(SUM(G61:G77),5)

The report has to be presented without the account detail, which has confidential information, and AutoFiltered to just show the totals of each category. In an extra column, I want to set up a formula to return either "Show" or "Hide", based on whether the formula is as a SUM formula, as opposed to a VLOOKUP. Is there a way to do that?

I am trying to have a vlookup return a blank if the value of the vlookup is greater than zero. Or, can a vlookup, only return a value once if the vlookup number is in the list multiple times.

Thanks.

I know this topic has been covered, but after scouring the forum, I havnt found a helpful answer to this degree.

I have a vlookup formula that is doing math. (right now it works but if I cannot figure how to alter to return zeros if no value is found and still work. Also i think its important to know that C3 is looking up a name of a person, but also sometimes that persons name will not be found, in which case i need it also to return a zero.

Thanks in advance for any help.

This is the formula that works:

=(VLOOKUP(C3,Netbook!\$E\$1:\$H\$307,2,FALSE)-VLOOKUP(C3,Netbook!\$E\$1:\$H\$307,3,FALSE)+(VLOOKUP(C3,Stick!\$E\$1:\$H\$307,2,FALSE)-VLOOKUP(C3,Stick!\$E\$1:\$H\$307,3,FALSE)))/H3

hey all,

can you use vlookup with offset? i am using vlookup but i want to return the cell value in the cell below the return i am getting..

sheet 1

Jan-01 1
Feb-02 2

sheet 2 (when i use formula below)

01 January 2001
1

Code:

=VLOOKUP(A1,Sheet1!\$A\$1:\$C\$17,2,1)

thats the normal lookup which returns the value 1 but i want to return the value below it which is 2. can this be done?

Hi Guys

Was just wondering if anyone out their would know how to have a vlookup look in two different workbooks. For example if the lookup did not return with a value then to lookup another workbook. The formula I have been working towards looks like this but I am having no luck.

=VLOOKUP(A1,[test2.xls]Sheet1!\$A:\$B,2,FALSE)=IF(B1<1,VLOOKUP(A1,[test3.xls]Sheet1!\$A:\$B,2,FALSE)

The first part is the vlookup and the second part is an =if forumula. So what I am trying to do is to do the vlookup and if the value does not return (which would be indicated by a value of 0, then do an =if argument so it does the lookup in another workbook. Does this make sense or am I way of the mark.

Please advice & many thanks!!

Hi,
I need to create a kind of a Vlookup formula that returns the value one cell bellow the usual Vlookup formula would return.
Example: =VLOOKUP(C5;E13:H41;4;FALSE) would return me the value in cell H21. All I need is to modify this formula somehow it would return me the valu in cell H22.

Can anyone help me, please?

apologies regarding the Title.
not sure of how to ask this question.
Vlookup can return the first reference to a specific value in a column, but if there is a 2nd or multiple references to the same value they cannot be returned.

Sorry i am confusing everybody

i will just explain the required end result.

how can i create a feature similar to a subform(Access) in Excel.
i have tried INDEX and MATCH, various LOOKUPS to find and return a value.
but i cannot figure out how to get the function to then move on to the next or subsequent corresponding value.

Hi

I have one master Workbook and 5 Data Workbooks
I would like to perform a Vlookup from the Master workbook and return Values from the 5 Data Workbook

I need to get this argument work somehow on Master Data Workbook

on ColumnC

If Column B is January Then VLookup on Workbook1 return value on Colum 4
If Column B is February Then VLookup on Workbook2 return value on Colum 4
If Column B is March Then VLookup on Workbook3 return value on Colum 4
If Column B is April Then VLookup on Workbook4 return value on Colum 4
If Column B is May Then VLookup on Workbook5 return value on Colum 4

On ColumnD

If Column B is January Then VLookup on Workbook1 return value on Colum 5
If Column B is February Then VLookup on Workbook2 return value on Colum 5
If Column B is March Then VLookup on Workbook3 return value on Colum 5
If Column B is April Then VLookup on Workbook4 return value on Colum 5
If Column B is May Then VLookup on Workbook5 return value on Colum 5

I am not sure if I should try a Macro or Formula, it is 80K row documents so I need to make sure it is efficient

Thank you