Lookup

[Mike_in_Apex]

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 ?

[#2]

If you have excel 365 then use XLOOKUP and it has the if_not_found parameter. If not then probably IFERROR( VLOOKUP(...), 0)

[#3 · Accepted solution]

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

Regards

https://www.upwork.com/freelancers/~01cf0fc8446b00f44c 

[#4]

@leoperdia 

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.

[#5]

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.

[#6]

@Mike_in_Apex 

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.

[#7]

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