VLOOKUP in Excel (2024)

The VLOOKUP function searches for a value in the left-most column of a table and returns the value on the same row of another column to the right. So you look up for "John" in column A and you get his address, his age or his balance that are in either columns B, C or D. It is not a bad function but there is much, much better.

VLOOKUP is an obsolete function inherited from Lotus-123. There is much better in Excel, more powerful and less limited, it is INDEX/MATCH. INDEX/MATCH replaces all lookups functions (VLOOKUP, HLOOKUP and LOOKUP). Here are usual questions from VLOOKUP users:

1- What if the look-up column inmy table is not the left-most column?

If you insist on using the VLOOKUP function it is complicated. You need to reorganize your table and make the look-up column the left most column of the table. If you are using INDEX/MATCH the look-up column can be anywhere.

2- What if the values in my lookup column are not in alphabetical order?

If you are using INDEX/MATCH instead of VLOOKUP it doesn't matter. If you use VLOOKUP you need to add a 5thargument to your formula (FALSE). This means that each time that you add a new row in your table you MUST re-sort it.

3- Can 2 criteria be used with VLOOKUP? For example, can I find John (column A) Baxter (column B)'s address (column C) or can I find the price (column D)of a 1978 (column A), Chevrolet (column B), Malibu (column C).

Yes, but it is complicated. You create a new column in which you concatenate the value of the criteria columns and you use this new column as criteria (first and last name or year, make and model). Remember that you need this new column to be the left-most column of the table.

If you are searching for a price (example 2) you just use a simple SUMPRODUCT formula with all three criteria. If you are looking for an address you will use INDEX/MATCH in array format.

4- In a sales database can I sum all the sales (column E) of one product (column B) with VLOOKUP?

No. Use SUMPRODUCT. For example, if in column A you have dates, in column B store numbers, in column C products and in column D amounts, you can with SUMPRODUCT sum the sales of a product, in a certain store and on a certain date. SUMPRODUCT is Excel's best kept secret and it is the ultimate tool to automate reports.

If you have read what is aove, I am surprised that you go on. But if you really insist....

The VLOOKUP function searches for value in the left-most column of a table and returns the value on the same row of another column to the right. So you look up for "John" in column A and you get his address, his age or his balance that are in columns in column B, C or D.

VLOOKUP in Excel is used in two situations:

- On an invoice or other documents you enter the name of a client and in the next few cells an Excel VLOOKUP formula brings in the address 1, address 2, the city, State and ZIP Code. Less manual entries less mistakes. On the same invoice you type in the product number and in other cells its description, unit and unit price are carried over by Excel VLOOKUP's.

- You have a set of sales data and you want to subtotal sales by cities. Unfortunately the city in which the client lives in not part of the set of data. This information exists in another set of data that you import on sheet 2. With Excel VLOOKUP you can bring the name of the city within your first set of data on sales and calculatethe subtotals by city.

IMPORTANT NOTE: The values that you are looking up through MUST BE IN ASCENDING ORDER (a, b, c, d) unless you use the fifth argument FALSE.

Here is a basic VLOOKUP formula: =VLOOKUP("John",A1:G32,2, FALSE)

The syntax for these formulas isas follows; the first argument ("John") is what you are looking for, the second argument (A1:G32) means to search a value in the table A1 to G32 looking up for "John" in column A, the third argument (2) is the column from which the answer is extracted, and the fifth argument (FALSE) tells Excel that you are looking for an EXACTmatch and not the next lower value. If you omit the fifth argument or use TRUE the formula will return the answer for the next lower value if it doesn't find EXACTLY what you are looking for and if the values are sorted in ascending order. For example if you are looking up for John and the formula only finds Albert and Susan it will return the answer for Albert.
=VLOOKUP("John",A1:G32,2, FALSE) looks for "John" in the first column (A) of the range (A1:G32) and returns the value of the cell in the 2nd column (B) same row.

You can also use the address of a cell in which you change the value as first argument in the formula (ex: J20)
=VLOOKUP(J20,A1:G32,2, FALSE) looks for whatever value is in cell J20 in the first column (A) of the range (A1:G32) and returns the value of the cell in the 2nd column (B) same row.

Remember to ALWAYS use "FALSE" as fifth argument and to make sure that the values in the column that you are looking up within are in ascending order.

VLOOKUP is always looking up within the first column of the table that you submit as second argument. If you want the column C to be the look up column you need to change the second argument to C1:G32.
=VLOOKUP(J20,C1:G32,2, FALSE).

VLOOKUP in Excel (2024)
Top Articles
Down Homepage
What to Do if Your Loan Application Is Denied - NerdWallet
Tyson Employee Paperless
Nyu Paralegal Program
Mackenzie Rosman Leaked
Culver's Flavor Of The Day Wilson Nc
St Als Elm Clinic
Lost Ark Thar Rapport Unlock
EY – все про компанію - Happy Monday
Arrests reported by Yuba County Sheriff
Craigslist/Phx
Nioh 2: Divine Gear [Hands-on Experience]
Buff Cookie Only Fans
Dr. med. Uta Krieg-Oehme - Lesen Sie Erfahrungsberichte und vereinbaren Sie einen Termin
Panorama Charter Portal
Vanessawest.tripod.com Bundy
Vrachtwagens in Nederland kopen - gebruikt en nieuw - TrucksNL
Skip The Games Fairbanks Alaska
Marine Forecast Sandy Hook To Manasquan Inlet
Universal Stone Llc - Slab Warehouse & Fabrication
Free Personals Like Craigslist Nh
The best brunch spots in Berlin
kvoa.com | News 4 Tucson
Urbfsdreamgirl
Keshi with Mac Ayres and Starfall (Rescheduled from 11/1/2024) (POSTPONED) Tickets Thu, Nov 1, 2029 8:00 pm at Pechanga Arena - San Diego in San Diego, CA
Sam's Club Gas Price Hilliard
Parent Management Training (PMT) Worksheet | HappierTHERAPY
Xfinity Outage Map Lacey Wa
Forager How-to Get Archaeology Items - Dino Egg, Anchor, Fossil, Frozen Relic, Frozen Squid, Kapala, Lava Eel, and More!
Netherforged Lavaproof Boots
Samsung 9C8
Reborn Rich Ep 12 Eng Sub
Edict Of Force Poe
Bimmerpost version for Porsche forum?
Austin Automotive Buda
Tiny Pains When Giving Blood Nyt Crossword
2007 Jaguar XK Low Miles for sale - Palm Desert, CA - craigslist
Lamont Mortuary Globe Az
Deepwoken: How To Unlock All Fighting Styles Guide - Item Level Gaming
Mychart University Of Iowa Hospital
Eat Like A King Who's On A Budget Copypasta
Studentvue Calexico
Conan Exiles Colored Crystal
Dragon Ball Super Card Game Announces Next Set: Realm Of The Gods
Meet Robert Oppenheimer, the destroyer of worlds
Headlining Hip Hopper Crossword Clue
Who Is Nina Yankovic? Daughter of Musician Weird Al Yankovic
The Hardest Quests in Old School RuneScape (Ranked) – FandomSpot
Itsleaa
Sunset On November 5 2023
Latest Posts
Article information

Author: Stevie Stamm

Last Updated:

Views: 5811

Rating: 5 / 5 (80 voted)

Reviews: 87% of readers found this page helpful

Author information

Name: Stevie Stamm

Birthday: 1996-06-22

Address: Apt. 419 4200 Sipes Estate, East Delmerview, WY 05617

Phone: +342332224300

Job: Future Advertising Analyst

Hobby: Leather crafting, Puzzles, Leather crafting, scrapbook, Urban exploration, Cabaret, Skateboarding

Introduction: My name is Stevie Stamm, I am a colorful, sparkling, splendid, vast, open, hilarious, tender person who loves writing and wants to share my knowledge and understanding with you.