4.1 VLOOKUP with Exact Match
Quick answer: the short, self-contained version of this lesson is the guide Excel VLOOKUP Exact Match (FALSE) for Beginners.
VLOOKUP exact match with example — in 30 seconds
What: VLOOKUP finds a value in the first column of a table and returns a value from another column of the same row.
Why: Orders only carry a Store ID. The report needs the City. VLOOKUP brings it across without copy-paste.
Tiny example: For Store ID BLK-NSK-01, =VLOOKUP(G2,Stores!$A$2:$F$8,3,FALSE) returns Nashik (City is column 3 of the Stores table).
VLOOKUP exact match with example — 30 सेकंदात
काय: VLOOKUP table च्या पहिल्या column मध्ये value शोधतो आणि त्याच row मधल्या दुसऱ्या column ची value परत देतो.
का: Orders मध्ये फक्त Store ID असतो. Report ला City हवी असते. VLOOKUP copy-paste न करता ती आणतो.
छोटं उदाहरण: Store ID BLK-NSK-01 साठी =VLOOKUP(G2,Stores!$A$2:$F$8,3,FALSE) उत्तर Nashik देतो (Stores table मध्ये City हा 3रा column आहे).
VLOOKUP exact match with example — 30 सेकंड में
क्या: VLOOKUP table के पहले column में value ढूँढता है और उसी row के दूसरे column की value लौटाता है.
क्यों: Orders में सिर्फ़ Store ID होता है. Report को City चाहिए. VLOOKUP बिना copy-paste के उसे ले आता है.
छोटा उदाहरण: Store ID BLK-NSK-01 के लिए =VLOOKUP(G2,Stores!$A$2:$F$8,3,FALSE) जवाब Nashik देता है (Stores table में City तीसरा column है).
First look at the data (before → after):
Expected result — City brought into Orders
| G: Store ID | P: City |
|---|---|
| BLK-NSK-01 | Nashik |
| AMN-NGP-01 | Nagpur |
| BLK-PUN-02 | Pune |
About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on vlookup with exact match.
Before table and formula as text
Before — Stores sheet (A: Store ID … E: City Manager)
| A: Store ID | C: City | D: Area | E: City Manager |
|---|---|---|---|
| BLK-PUN-01 | Pune | Kothrud | Ravindra Bagale |
| BLK-PUN-02 | Pune | Hinjewadi | Shraddha Bagale |
| BLK-NSK-01 | Nashik | College Road | Zoya |
| AMN-NGP-01 | Nagpur | Dharampeth | Amir |
| BLK-KOP-01 | Kolhapur | Rajarampuri | Rani |
| AMN-SLP-01 | Solapur | Murarji Peth | Salman |
| BLK-SBN-01 | Sambhaji Nagar | CIDCO | Raja |
About this data: Demo table only — not real customer or company data. Built to practise vlookup with exact match.
Formula / click path
Orders!P2: =VLOOKUP(G2,Stores!$A$2:$F$8,3,FALSE)
FALSE = exact match · 3 = third column of A:F
Do it step by step:
- In the Orders sheet, click P2.
- Type
=VLOOKUP(G2,and then select Stores!A2:F8. - Press F4 so the range becomes
Stores!$A$2:$F$8. - Type
,3,FALSE)and press Enter. - Double-click the fill handle to copy down.
स्टेप बाय स्टेप करून बघा:
- Orders sheet मध्ये P2 वर click करा.
=VLOOKUP(G2,type करा आणि मग Stores!A2:F8 select करा.- F4 दाबा, म्हणजे range
Stores!$A$2:$F$8होईल. ,3,FALSE)type करा आणि Enter दाबा.- Fill handle वर double-click करून खाली copy करा.
स्टेप बाय स्टेप करके देखें:
- Orders sheet में P2 पर click करें.
=VLOOKUP(G2,type करें और फिर Stores!A2:F8 select करें.- F4 दबाएँ, ताकि range
Stores!$A$2:$F$8बन जाए. ,3,FALSE)type करें और Enter दबाएँ.- Fill handle पर double-click करके नीचे copy करें.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value – what to find (e.g. the Store ID in the order row)
- table_array – the lookup table; the lookup value must be in its first column
- col_index_num – which column of the table to return (1 = first column)
- range_lookup –
FALSE(or 0) = exact match;TRUE(or omitted) = approximate
Steps in Excel – bring City into Orders
- In
Orders, Store ID is in column G. Click the first empty column, e.g. P2. - Type
=VLOOKUP(G2,then go toStores, select A2:F8, press F4 to lock it:Stores!$A$2:$F$8. - Type
,3,FALSE)(City is the 3rd column) › Enter. - Double-click the fill handle to copy down.
| Formula | Result |
|---|---|
=VLOOKUP("BLK-NSK-01",Stores!$A$2:$F$8,3,FALSE) |
Nashik |
=VLOOKUP("AMN-NGP-01",Stores!$A$2:$F$8,5,FALSE) |
Amir |
=VLOOKUP("BLK-PUN-09",Stores!$A$2:$F$8,3,FALSE) |
#N/A (not in master) |
About this data: Made-up rows so you can copy the pattern for vlookup with exact match. Currencies may use ₹, £, € or $.
Ravindra Bagale's Tip
The most common VLOOKUP mistake is not writing the final FALSE. Then Excel does an approximate match and gives a wrong answer that "looks right" – without any error! For an exact match, always write FALSE or 0, and put $ on the table_array.
Ravindra Bagale's Tip – मराठी
सगळ्यात common VLOOKUP चूक म्हणजे शेवटचा FALSE न लिहिणं. मग Excel approximate match करतो आणि चुकीचं पण "खरं वाटणारं" answer देतो – error पण येत नाही! Exact match साठी नेहमी FALSE किंवा 0 लिहा, आणि table_array ला $ लावा.
Ravindra Bagale's Tip – हिंदी
सबसे common VLOOKUP गलती है आख़िरी FALSE न लिखना. फिर Excel approximate match करता है और गलत पर "सही लगने वाला" answer देता है – error भी नहीं आता! Exact match के लिए हमेशा FALSE या 0 लिखो, और table_array पर $ लगाओ.
Practice task
Bring Area, City Manager and Manager Email into Orders with three VLOOKUPs. Add a Store ID that does not exist and observe the #N/A.
Loading practice tasks...
- City of a store (Easy): Find the City (column 3) of BLK-NSK-01.
- Manager of a store (Easy): Find the City Manager of AMN-NGP-01.
- Pharmacy: medicine name (Easy): Find the Medicine (column 2) of MED-102.
- Pharmacy: price (Easy): Find the price of MED-104.
- Use a cell (Medium): Find the Area (column 4) of the store in H2.
- Platform of a store (Medium): Find the Platform of AMN-SLP-01.
- Pharmacy: stock (Medium): Find the Stock of MED-103.
- Pharmacy: pack size (Medium): Find the Pack of MED-101.
- Year opened (Hard): Find the year (column 6) the store in H2 opened.
- Pharmacy: bill for 3 packs (Hard): Find the cost of 3 packs of MED-101.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
