Issues with VLOOKUP in Excel, #Value

0
votes

I have Excel file where data is automatically input into varies sheets. I am trying to develop a new sheet to display some of the important information from some of the other sheets. I got most of the cells on my sheet done but two are giving me some problems. The problem is that I am using VLOOKUPS/ ISERROR and VLOOKUPS/ ISERROR. My problem is when we combine to separate answers that yield no result. I want to know if there was a way to prevent this to from showing " # VALUE! " when one or both of the VLOOKUPS/ ISERROR statements is false.

Below is a copy of the formula that I am using for the cells, and below that is an example of the data that I would receive.

=IF(ISERROR(VLOOKUP(22, ChargingData!$B$31:$I$41,2, FALSE)),"",VLOOKUP(22, ChargingData!$B$31:$I$41,2, FALSE)/2000) + IF(ISERROR(VLOOKUP(33, ChargingData!$B$31:$I$41,2, FALSE)),"",VLOOKUP(33, ChargingData!$B$31:$I$41,2, FALSE)/2000)

=IF(ISERROR(VLOOKUP(23, ChargingData!$B$31:$I$41,2, FALSE)),"",VLOOKUP(23, ChargingData!$B$31:$I$41,2, FALSE)/2000) + IF(ISERROR(VLOOKUP(33, ChargingData!$B$31:$I$41,2, FALSE)),"",VLOOKUP(33, ChargingData!$B$31:$I$41,2, FALSE)/2000)

Table data

33  65456            868931           654964             989872
23          65685    456331           6568     789911    65464
22          35468             84213   654987             633314

I was wondering if someone could help me to stop the table from show the "# VALUE!" message. Instead of that message I would like to display, " ", if there nothing in any of the cells and displays the correct information if there is data in any of the necessary cells. Thanks

3
excelnullvlookup
The formulas work for me, I get 32.768 as the answer, but I do notice that the 2nd VLOOKUP has a slightly different range - SeanC
I was looking at the vlookup and noticed that just now but that wasn't the issue. - LaDante Riley
The problem will occur when one VLOOKUP returns "" and the other a number - you can't add "" to a number (you get a #VALUE! error). Which version of Excel are you using? Are you wedded to VLOOKUP here? If 22 and 23 only occur at most once in the lookup range then SUMIF might be preferable.....e.g. =SUM(SUMIF(ChargingData!$B$31:$B$41,{22,23},ChargingData!$C$31:$C$41))/2000 - barry houdini
....Oh and of course ""+"" also gives you VALUE, if you want to stick with VLOOKUP try using 0 instead of "" and then if you don't want to see zeroes in the results format cells to show zero as blank [e.g. custom format General;;]. If you are using Excel 2007 or later then use IFERROR function to shorten the formulas - barry houdini

3 Answers

0
votes

Try this (my parenthesis may be a little off):

=IF(Or(ISERROR(VLOOKUP(23, ChargingData!$B$31:$I$41,2, FALSE),ISERROR(VLOOKUP(33, ChargingData!$B$31:$I$41,2, FALSE))),"",(VLOOKUP(23, ChargingData!$B$31:$I$41,2, FALSE)/2000) + (VLOOKUP(33, ChargingData!$B$31:$I$41,2, FALSE)/2000)))

To break it down:

If either vLookups are errors

=IF(Or(ISERROR(VLOOKUP(23, ChargingData!$B$31:$I$41,2, FALSE),ISERROR(VLOOKUP(33, ChargingData!$B$31:$I$41,2, FALSE)))

Return Blank

,"",

Otherwise, return the first Vlookup / 2000 + the second Vlookup / 2000

(VLOOKUP(23, ChargingData!$B$31:$I$41,2, FALSE)/2000) + (VLOOKUP(33, ChargingData!$B$31:$I$41,2, FALSE)/2000)))
0
votes

I happened to notice that something about how the data comes in. Somehow the data comes in string characters instead of generic characters hence the "" at the start of the VLOOKUP. Taking advice from Barry Houdini, I replaced the return "" for the VLOOKUP to return 0 if the VLOOKUP table is empty/missing. I added the the extra IF statement to show blank if returns 0.

Below is the new function that I am using for the cells:

=IF(IF(ISERROR(VLOOKUP("23", ChargingData!$B$31:$I$41,2, FALSE)),0,VLOOKUP("23", ChargingData!$B$31:$I$41,2, FALSE)/2000)+ IF(ISERROR(VLOOKUP("33", ChargingData!$B$31:$I$41,2, FALSE)),0,VLOOKUP("33", ChargingData!$B$31:$I$41,2, FALSE)/2000)<>0,IF(ISERROR(VLOOKUP("23", ChargingData!$B$31:$I$41,2, FALSE)),0,VLOOKUP("23", ChargingData!$B$31:$I$41,2, FALSE)/2000)+ IF(ISERROR(VLOOKUP("33", ChargingData!$B$31:$I$41,2, FALSE)),0,VLOOKUP("33", ChargingData!$B$31:$I$41,2, FALSE)/2000),"")

0
votes

Try this:

=numbervalue(ifna(VLOOKUP(23, ChargingData!$B$31:$I$41,2, FALSE),0))/2000+ 
 numbervalue(ifna(VLOOKUP(33, ChargingData!$B$31:$I$41,2, FALSE),0))

numbervalue() change string to number ("" => 0) ifna() if value not found, return 0