Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Vlookup, Return Zero If Not Found

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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.


View Answers     

Similar Excel Video Tutorials

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







Ok, yet more fun! With the same prohject as before, I have realised that there are some issues...

Are there anyway of making a VLOOKUP conditional on whether or not there is actually an item where it is looking? Heres the coding and a better explanatoin:

Code:

=D3*(VLOOKUP(E3,Lookups!$A$2:$B$41,2))+(VLOOKUP(Q3,INDIRECT(VLOOKUP(E3,Lookups!D2:E17,2)),2))+(VLOOKUP(R3,INDIRECT(VLOOKUP(E3,Lookups!D2:E17,2)),2))+(VLOOKUP(S3,INDIRECT(VLOOKUP(E3,Lookups!D2:E17,2)),2))+(VLOOKUP(T3,INDIRECT(VLOOKUP(E3,Lookups!D2:E17,2)),2))+(VLOOKUP(U3,INDIRECT(VLOOKUP(E3,Lookups!D2:E17,2)),2))


Long isn't it....

So heres my problem, I want the last 5 VLOOKUPS only to happen if there is actually data for them to source from (Q3:U3) So if i put one of the tmers in Q3 and nothing in the others, it will still give me a result as opposed to '#N/A'

Any ideas guys??




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!


Happy Christmas and New Year,

I'm hoping someone more intelligent than myself can get out of a bind. I have multiple ranges in a spread sheet. I am trying to write a formula that will go out to each range in succession and look for a part number, upon finding return a quantity and them move on to the next range duplicating the above process. The formula should tally the grand total of all numbers found. I have it working except that not all of my items are in all ranges. If the item that I am searching for is in all ranges my formula works but if there is one or more of the ranges that doesn't have that particular value it returns an #n/a instead of totalling those that do have it. If I use a true instead of false in my [range_lookup] I get an incorrect answer. My formula for a given cell is listed below. This is with the true argument which does not work.

=SUM(VLOOKUP($A135,$P$104:$S$170,4,TRUE)+VLOOKUP($A135,$T$104:$W$170,4,TRUE)+VLOOKUP($A135,$X$104:$A A$170,4,TRUE)+VLOOKUP($A135,$AB$104:$AE$170,4,TRUE)+VLOOKUP($A135,$AF$104:$AI202,4,TRUE)+VLOOKUP($A1 35,$AJ$104:$AM$170,4,TRUE)+VLOOKUP($A135,$AN$104:$AQ$170,4,TRUE)+VLOOKUP($A135,$AR$104:$AU$170,4,TRU E)+VLOOKUP($A135,$AV$104:$AY$170,4,TRUE)+VLOOKUP($A135,$AZ$104:$BC$170,4,TRUE)+VLOOKUP($A135,$BD$104 :$BG$170,4,TRUE)+VLOOKUP($A135,$BH$104:$BK$170,4,TRUE)+VLOOKUP($A135,$BL$104:$BO$170,4,TRUE)+VLOOKUP ($A135,$BP$104:$BS$170,4,TRUE)+VLOOKUP($A135,$BT$104:$BW$170,4,TRUE)+VLOOKUP($A135,$BX$104:$CA$170,4 ,TRUE)+VLOOKUP($A135,$CB$104:$CE$170,4,TRUE)+VLOOKUP($A135,$CF$104:$CI$170,4,TRUE))


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,


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 am trying to use vlookup to lookup an array of inputs, and then return an array of outputs. Then, I want to average over these outputs. I'd like to do all of this in one function call. Is it possible to do this, or to do something similar to achieve the same result?

I am trying to do it, and it's not working.

This is what my formula says:

{=AVERAGE(VLOOKUP(B9:B10,B4:C6,2,0))}

cells B4:C6 contain the an array that looks like this:

a 1
b 2
c 3

Cells B9:B10 are as follows:

a
b

Thus, I want the vlookup to return the array {1,2}, average over those, and give 1.5. Instead, it returns 1.

My spreadsheet is enclosed. As you can see, B13 returns 1 instead of 1.5.

Thanks,
Jonathan


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


Hi,
When I run a Vlookup for 'X' on a list which has many 'X' values, Excel only returns the bottom value in the result. Is it possible to return the Sum of the Xs?
Also, is it possible to make the Vlookup return multiple columns instead of just one?

Thanks


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


Hi, I am trying to create an automated receipt for a list of artists. I have given the artist an individual reference so I am doing a VLOOKUP to return description/price value etc but I can only get it to return the initial value and I have approx 10-15 works under each reference. Maybe VLOOKUP is the wrong formula to use.. I have attached the file - if some excel whizz out there can give me any ideas it would be a huge help!

thanks


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


Hi guys,

Here's what my data looks like: (ignore the '.' - I'm putting it in to make the columns line up)

company day return
a...............1..........1%
a...............2..........3%
a...............3...........-5%
a...............4..........1%
a...............5..........2%
b...............1...........1%
b...............2............2%
b...............3...........3%
b...............4............5%
b...............5............6%

And I want to create a smaller table based off this data,

company day return
a.................2.............?

e.g. show me company a's return on day 2

I've been trying to use this formula:

=VLOOKUP(B13,INDIRECT(VLOOKUP(A13,A1:C7,3,FALSE)),3,FALSE)

I was trying to tell excel to first find all the company codes in the main data sheet, then match the day in order to give me one return number. This formula just gives me a #REF! error though

I'm running excel 07, windows vista just in case that's important to know.

Is there someway to combine multiple vlookups within the one formula?

Thanks!


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