Forum Discussion
Solved
Lookup
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 ?
Hi Mike_in_Apex this function should work =IFNA(VLOOKUP(lookup range;lookup value;result column;FALSE);0)
Regards
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.
Hi Mike_in_Apex this function should work =IFNA(VLOOKUP(lookup range;lookup value;result column;FALSE);0)
Regards
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.
- If you have excel 365 then use XLOOKUP and it has the if_not_found parameter. If not then probably IFERROR( VLOOKUP(...), 0)
- 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.
