Copper Contributor

I have a location number.  I have an array of results for certain locations.  I want to look up for any results for a location and if so put the results in a cell, otherwise I want to put the value "0" in the cell.  I've tried an IF function, with a VLOOKUP  function essentially saying if the VLOOKUP is any value ( i.e. greater than zero) then return the results of the VLOOKUP or else return a "0".    Would I be better off trying INDEX or IFNA or IFERROR ?

6 Replies
If you have excel 365 then use XLOOKUP and it has the if_not_found parameter. If not then probably IFERROR( VLOOKUP(...), 0)
best response confirmed by Mike_in_Apex (Copper Contributor)

Hi @Mike_in_Apex  this function should work =IFNA(VLOOKUP(lookup range;lookup value;result column;FALSE);0)




I had tried this on the particular version of a spreadsheet that someone had sent me and it didn't work.  It probably was created by a different version of Excel.  I'm using Excel 2016.  When I created my own test and used your answer, it worked just fine.  Thanks for your response.


I am using Excel 2016, but the spreadsheet in question which was sent to me may have been created in Excel 365, so "my" solution didn't work. Thanks for your response.


If you are using excel 2016, you can use the combination of VLOOKUP and IFERROR functions to get your desired result.

Here is an example formula for implementation:

=IFERROR(VLOOKUP(location_number, lookup_array, column_index, FALSE), 0)

Replace the following parts in the formula:

  • location_number: The cell reference or value representing the location number you want to search.
  • lookup_array: The range of cells where you have the locations and their corresponding results. Make sure the location numbers are in the leftmost column of this range.
  • column_index: The column number in the lookup_array from which you want to retrieve the result. Adjust this number based on your actual setup.

The formula will perform a VLOOKUP to search for the location number in the lookup_array. If a match is found, it will return the corresponding result. If there is no match, the IFERROR function will capture the error and return a "0" instead.

Using IFERROR in conjunction with VLOOKUP is a good approach in this scenario, as it simplifies the formula and handles the error condition effectively.

The steps were processed with the help of AI.

IFNA and VLOOKUP in combination also works. Thanks for your response.