Filling down a VLOOKUP formula without changing the range

excel excel-formula vlookup worksheet-function
Andrew picture Andrew · Feb 27, 2015 · Viewed 12.5k times · Source

I am comparing values in a row in one sheet to values in another row in another sheet. The following formula and works:

=IFERROR(VLOOKUP(A1,Sheet1!A1:A19240,1,FALSE),"No Match")

My problem is when I fill down the formula, it increments A1 correctly but also increments the (A1:A19240), so half way down I have narrowed the search field.

How can I apply this formula to a column?

Answer

pnuts picture pnuts · Oct 5, 2015

Change A1:A19240 to A$1:A$19240, i.e. apply: 

=IFERROR(VLOOKUP(A1,Sheet1!A$1:A$19240,1,FALSE),"No Match")

This is called using absolute references.

Related questions

How to use VLOOKUP result as COUNTIF criteria

I want to be able to determine how many times a person's particular ID (e.g. #123) has appeared in a separate column (e.g. column C) given a name (e.g. Bob). I have: Let column A consist of several …

Read More
How to categorize/classify numbers from different ranges using Excel?

I want to categorize numbers from [0:15] into 5 classes: A, B, C, D, E, F Rules: 0-1: Class A 2-4: Class B 5-7: Class C 8-10: Class D 11-13: Class E 14-15: Class F Explanation: if number is in range [0:1] then …

Read More
Excel - find cell with same value in another worksheet and enter the value to the left of it

I have a report that is generated in Excel which contains an employee's number, but not his/her name. Not every employee will be on this worksheet on any given day. In a 2nd worksheet I have a list of …

Read More