How to Populate Village from House Number: VLOOKUP

This post illustrates the use of VLOOKUP.

You have an Excel spreadsheet table in which each row contains member data. It has a house number column. You want to fill another column with the corresponding village for each member (row).

You can achieve this in Excel using a combination of VLOOKUP, IF, and setting up a separate lookup table.

Here are the steps:

1. Create a Lookup Table (Village Ranges)

First, organize your village and house number ranges into a separate table. This makes the VLOOKUP function much easier to use. (The below table is not meant to be 100% accurate; some house numbers in the ranges do not exist. But if you assume a member's house number is valid, it gives you the village name correctly. Which is all we need.)

Min_House_Number	Max_House_Number	Village
2000	2101	Sonata
3101	3419	Del Lago
4001	4024	Fairways
5001	5576	Cribari
6001	6361	Montgomery
7001	7404	Verano
7500	7701	Highland
7702	7867	Glen Arden
7900	7949	Highland
8005	8447	Hermosa
8448	8519	Heights
8600	8897	Olivas
9001	9072	Valle Vista

Note for Highland: Since Highland has two non-contiguous ranges, you'll need to list it twice in your lookup table, once for each range.

2. Your Main Data Table

Let's assume your house numbers are in Column A of your main sheet, starting from A2.

House Number	Village
2050
3150
7550
7920
8450
9010

3. The Excel Formula

Now, in the "Village" column (e.g., cell B2) of your main data table, you'll enter a formula. Because Highland has two ranges, you'll need to use nested IF statements. A combination of VLOOKUP and IF will work well.

Assuming your lookup table is on a sheet named "Lookup" and occupies cells A2:C14:

Excel formula:

=IFERROR(VLOOKUP(A2,Lookup!$A$2:$C$14,3,TRUE),IF(AND(A2>=7900,A2<=7949),"Highland","Not Found"))

Let's break down this formula:

• VLOOKUP(A2,Lookup!$A$2:$C$14,3,TRUE):

  • A2: This is the house number you're looking up.

  • Lookup!$A$2:$C$14: This is your lookup table range. Make sure to use absolute references ($) so it doesn't change when you drag the formula down.

  • 3: This indicates that you want to return the value from the third column of your lookup table (the "Village" column).

  • TRUE: This is crucial. It means Excel will look for an approximate match. It will find the largest value in the first column of the lookup table that is less than or equal to A2. This works perfectly for ranges.

• IFERROR(..., ...):

  • This function catches errors. If the VLOOKUP doesn't find a match (e.g., the house number is outside any defined range or falls into the second Highland range which VLOOKUP might miss if it finds the first one), it will execute the second part of the IFERROR.

• IF(AND(A2>=7900,A2<=7949),"Highland","Not Found"):

  • This is the specific handling for the second "Highland" range.

  • AND(A2>=7900,A2<=7949): Checks if the house number falls within the second Highland range.

  • "Highland": If it does, "Highland" is returned.

  • "Not Found": If it still doesn't match anything, it will display "Not Found" (you can customize this text).

4. Drag Down the Formula

After entering the formula in B2, click on cell B2, then drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formula to all your house numbers.

How VLOOKUP with TRUE works for ranges:

When you use TRUE for the range_lookup argument in VLOOKUP, Excel expects the first column of your lookup table to be sorted in ascending order. It then performs an approximate match. It finds the largest value in the first column that is less than or equal to the lookup value. Then, it returns the corresponding value from the specified column.

For example, if you look up 2050:

1. Excel looks for 2050 in the "Min_House_Number" column.

2. It finds 2000 (which is less than or equal to 2050).

3. It then returns "Sonata" from the third column.

If you look up 7550:

1. It finds 7500 (which is less than or equal to 7550).

2. It then returns "Highland".