## What to do when Vlookup returns NA?

**Problem: The lookup column is not sorted in the ascending order**

- Change the
**VLOOKUP**function to look for an exact match. To**do**that, set the range_lookup argument to FALSE. No sorting is necessary for FALSE. - Use the INDEX/MATCH function to look up a value in an unsorted table.

## Can Vlookup return multiple matches?

Well, **VLOOKUP** is designed to **return** a single value, not **multiple values**.

## What does N A mean in Vlookup?

In **Vlookup** formulas, the #**N**/A error message (**meaning** “not available”) is displayed when Excel cannot find a lookup value.

## How do I avoid Na in Vlookup?

**VLOOKUP** without #**N**/A error

- Generic formula. =IFERROR(
**VLOOKUP**(value,table,2,FALSE),”message”) - Summary. To hide the #
**N**/A error that**VLOOKUP**throws when it can’t find a value, you can use the IFERROR function to catch the error and return any value you like. - When
**VLOOKUP**can’t find a value in a lookup table, it returns the #**N**/A error.

## Why is my Vlookup returning NA when value exists?

**The** most common cause of **the** #**N/A** error is with **VLOOKUP**, HLOOKUP, LOOKUP, or MATCH functions if a formula can’t find a referenced **value**. For example, your lookup **value** doesn’t **exist** in **the** source data. In this case there is no “Banana” listed in **the** lookup table, so **VLOOKUP** returns a #**N/A** error.

## How do I Vlookup multiple values in one cell?

To get **multiple** lookup **values** in a **single cell**, we need to create a function in VBA (similar to the **VLOOKUP** function) that checks each **cell** in a column and if the lookup **value** is found, adds it to the result.

## Can you do a Vlookup on multiple columns?

The **VLOOKUP** function **can** be combined with other functions such as the Sum, Max, or Average to calculate values in **multiple columns**. As this is an array formula, to make it work **we** simply need to press CTRL+SHIFT+ENTER at the end of the formula.

## How do you combine Vlookup And if?

**Combine IF** Function with **VLOOKUP**

- Select cell E2 by clicking on it.
- Assign the formula =
**IF**(**VLOOKUP**(D2,A2:B6,2,FALSE)>2500,”Yes”,”No”) to cell E2. - Press Enter to apply the formula in cell E2.

## What is the alternative for Vlookup?

VLOOKUP is one of those functions that make **Excel** great. Although many may not find it intuitive at first, it soon becomes second nature and indispensable.

## Why am I getting #ref in Vlookup?

In the following example, =**VLOOKUP**(A8,A2:D5,5,FALSE) will return a #**REF**! error because it’s looking for a value to return from column 5, but the reference range is A:D, which is only 4 columns. Adjust the range to be larger, or reduce the column lookup value to match the reference range.

## Why is Vlookup showing 0?

However, when the result in a lookup table is an empty cell, **no** error is thrown, **VLOOKUP** simply **returns** a **zero**. This can cause problems when the lookup table contains actual **zero** values, because it suggests that blank cells in the lookup table also contain zeros, when they in fact are empty.

## How do I remove Vlookup and keep formula in Excel?

**Delete** a **formula** but **keep** the results

- Select the cell or range of cells that contains the
**formula**. - Click Home > Copy (or press Ctrl + C).
- Click Home > arrow below Paste > Paste Values.

## How do I remove NA values in Excel?

Use the formula =IFNA(A1,””) in an adjacent column and copy downwards. The second argument of this function is the **value** that you want to replace a cell containing #N/A with. I’ve chosen a blank string, but you could use 0, &c.

## How do I replace blanks with 0 in Excel?

Use **Excel’s** Find/**Replace** Function to **Replace Zeros**

Choose Find/**Replace** (CTRL-H). Use for Find what and leave the **Replace** with field **blank** (see below). Check “Match entire cell contents” or **Excel** will **replace** every **zero**, even the ones within values.