Excel Snippets - Tools




Excel VLOOKUP #N/A Suppression

One of the problems when using a basic VLOOKUP query in Excel is that Excel will return an '#N/A' error if it cannot find a value.

It can look untidy in a spreadsheet and you may prefer that the value returned should be a blank or a zero if there is no match.


This error message can be supressed in a formula by combining the VLOOKUP formula with the ISNA function, but it can be a little tricky to compose.


Below is a tool that automates the process of adapting a single VLOOKUP/HLOOKUP formula to add the ISNA function to return a blank or zero value instead.


To use the tool, paste in a single VLOOKUP or HLOOKUP formula into the textbox and click the 'Submit' button to add the extra code.

For example, paste in:   =VLOOKUP(H8,Sheet2!A1:B3,2,FALSE)


Paste your basic VLOOKUP/HLOOKUP formula here:


Choose a value to substitute the N/A message with (Blank cell or 0):


Index Match Formula Creator (Standard VLOOKUP Equivalent)

Formulas INDEX and MATCH can be combined to produce results similar to VLOOKUP and HLOOKUP, but are more flexible in their operation. For example, VLOOKUP requires that the data that you are comparing is contained in the first column supplied. INDEX/MATCH is not constrained to this limitation. INDEX/MATCH also performs faster than VLOOKUP/HLOOKUP.


Below is a tool that automates the creation of an INDEX/MATCH formula that replicates a standard VLOOKUP function.
The Excel screenshot can be used as a guide for finding the fields necessary.

Fill in the following fields and click the 'Submit' button to generate a formula below the form:





Type in the cell reference of the cell in the current row, that you want to compare to find a corresponding value:
Orange cell in screenshot


Type in the name of the worksheet that contains the data to be looked up
Leave this value blank if the data to be found is on the same worksheet:


Type in the two dimensional array or range name that you want to look up your data in(must contain more than one column)
It is recommended that you use $ symbols to denote an absolute reference if the formula is to be copied and pasted elsewhere:
Green border in screenshot


Type in the one dimensional array or range name that contains the value you want to match(must be the same height as the two dimensional array)
It is recommended that you use $ symbols to denote an absolute reference if the formula is to be copied and pasted elsewhere
Unlike VLOOKUP, this does not have to be the first column in the supplied two dimensional array:
Purple cell range in screenshot


Type in the number of columns along, from the start of the two dimensional array that the value you want to find is located in(column number starts at 1)


Type in the match type number:
0  = the equivalent to VLOOKUP false option - exact match
1  = the equivalent to VLOOKUP true option, match the largest value, less than or equal to - data must be sorted in ascending order
-1 = match the smallest value, greater than or equal to - data must be sorted in descending order
Caution: The MATCH formula uses a default of 1 if this is not set:


Index Match Formula Creator (X/Y Lookup)

Below is a tool that automates the creation of an INDEX/MATCH formula that performs an X/Y lookup.
The Excel screenshot can be used as a guide for finding the fields necessary.

Fill in the following fields and click the 'Submit' button to generate a formula below the form:





Type in the cell reference of the cell that contains the value of the vertical axis of the lookup:
Blue cell in screenshot


Type in the cell reference of the cell that contains the value of the horizontal axis of the lookup:
Orange cell in screenshot


If the source data is on another worksheet, type in the name of the worksheet(otherwise leave this field blank):


Type in the two dimensional range or the range name (this should only contain the data, rather than any index values):
It is recommended that you use $ symbols to denote an absolute reference if the formula is to be copied and pasted elsewhere
Green cell range in screenshot


Type in the one dimensional range or the range name of the vertical index which is to be looked up:
It is recommended that you use $ symbols to denote an absolute reference if the formula is to be copied and pasted elsewhere
Yellow cell range in screenshot


Type in the one dimensional range or the range name of the horizontal index which is to be looked up:
It is recommended that you use $ symbols to denote an absolute reference if the formula is to be copied and pasted elsewhere
Purple cell range in screenshot


Type in the match type number:
0  = the equivalent to VLOOKUP false option - exact match
1  = the equivalent to VLOOKUP true option, match the largest value, less than or equal to - data must be sorted in ascending order
-1 = match the smallest value, greater than or equal to - data must be sorted in descending order
Caution: The MATCH formula uses a default of 1 if this is not set:


IF AND/OR Formula Generator

Below is a tool which will compose an Excel IF statement from your provided data. It will also combine this with AND/OR conditions if there is a second logical test typed in the box.


Tip: You could use this with the formulas generated above if you want to create long formulas using VLOOKUPS or INDEX/MATCH formulas combined with ISNA conditions.


Type in the first logical test:
Example: A1=5



Choose either AND or OR to combine another conditional test(optional):



Type in the second logical test: (leave blank if you don't want the extra condition)
Example: B5="BLUE"



Type in the value to assign or type/paste in a formula to calculate if the result is TRUE:



Type in the value to assign or type/paste in a formula to calculate if the result is FALSE: