Ravindra BagaleCourses & study guides मराठी Track your progress

Excel VLOOKUP Explained: Exact Match, Column Number, Duplicates and #N/A (with HLOOKUP and TRANSPOSE)

By Last updated:

MAX tells you the highest percentage in the sheet is 90. It does not tell you who scored it. Every function so far worked on numbers; now we need to search: type a name and get that student's total and result, without scrolling through thousands of rows. That is what lookup functions do. This chapter covers the oldest and most-asked one, VLOOKUP, its rules and traps, plus its sideways twin HLOOKUP and the TRANSPOSE function that turns a table on its side.

What you'll learn in this chapter

  • Why we need lookup functions
  • The three rules of VLOOKUP
  • The four arguments and how to count the column number
  • FALSE for exact match, and what TRUE really does
  • Duplicates: VLOOKUP returns the first match from the top

  • N/A and how to show "Not found" instead

  • Three columns with one VLOOKUP
  • HLOOKUP and TRANSPOSE

Practice file: excel-vlookup-hlookup-practice.xlsx has the VLOOKUP sheet, the HLOOKUP sheet and a Your turn sheet.

1. The student table

The same eleven students as the IFS grades chapter, now with total, result and percentage already calculated. Data is in A5:H15, headers in row 4.

roll name maths science english total result percentage
101 Ravindra 78 85 66 229 Pass 76.33
102 Shraddha 92 88 90 270 Pass 90.00
103 Ruhi 34 71 58 163 Fail 54.33
104 Salman 88 35 72 195 Pass 65.00
105 Shahrukh 56 49 30 135 Fail 45.00
106 Zoya 67 90 84 241 Pass 80.33
107 Amir 40 62 47 149 Pass 49.67
108 Ravina 45 28 52 125 Fail 41.67
109 Raja 60 40 38 138 Pass 46.00
110 Rani 81 76 89 246 Pass 82.00
111 Salman 30 55 41 126 Fail 42.00

Classroom line

All the functions we have seen so far worked on numbers.

The goal: a small search box. Type a name in J5, and K5 shows that student's total automatically.

2. The three rules of VLOOKUP

  1. The table is vertical: one record per row, headings across the top. ("V" is for vertical.)
  2. It searches only the first column of the range you select and returns a value from a column to its right. If you search by name, the name column must be the first column of your range, and VLOOKUP can never return the roll number that sits to the left of it.
  3. It returns the first match from the top. If a name appears twice, you get the upper one.

3. The four arguments

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

  1. lookup_value: what you search for, here the name in J5.
  2. table_array: the range, starting at the column you search in: $B$5:$H$15 (name to percentage). Lock it with $ so it does not move when you drag the formula.
  3. col_index_num: which column of that range to return, counted from 1 = the first column of the range (not column A of the sheet). Name is 1, maths 2, science 3, english 4, total 5, result 6, percentage 7.
  4. range_lookup: FALSE (or 0) for an exact match. Always use FALSE for names, IDs and codes.

How:

  1. Type Zoya in J5.
  2. In K5 type =VLOOKUP(J5,$B$5:$H$15,5,FALSE) → 241.
  3. Column 6 gives Pass, column 7 gives 80.33.
VLOOKUP searches the first column, returns column 5 B C D E F G H 4 name maths science english total result percentage 5 Ravindra 78 85 66 229 Pass 76.33 6 Shraddha 92 88 90 270 Pass 90.00 7 Ruhi 34 71 58 163 Fail 54.33 8 Salman 88 35 72 195 Pass 65.00 9 Shahrukh 56 49 30 135 Fail 45.00 10 Zoya 67 90 84 241 Pass 80.33 11 Amir 40 62 47 149 Pass 49.67 12 Ravina 45 28 52 125 Fail 41.67 13 Raja 60 40 38 138 Pass 46.00 14 Rani 81 76 89 246 Pass 82.00 15 Salman 30 55 41 126 Fail 42.00 1 2 3 4 5 6 7 Yellow: Zoya found in column 1. Green: columns 2-4 skipped, column 5 returned. =VLOOKUP(J5,$B$5:$H$15,5,FALSE) 1 lookup_value J5 = "Zoya" 2 table_array $B$5:$H$15, starts at name 3 col_index_num 5 = 5th column of the range 4 range_lookup FALSE = exact match J5: Zoya K5: 241 Column 4 instead of 5 → 84 (English): no error, just a wrong answer.

Figure 1. VLOOKUP searches Zoya in the first column of B5:H15 and returns the 5th column of that range: total 241.

Count the column number carefully. With 4 instead of 5 you get 84, Zoya's English mark: no error, just a wrong answer that looks right. Put the column numbers above your headers once (1, 2, 3 …) while you learn; it removes the guesswork.

Ravindra Bagale's Tip

Change the name in J5 to two or three people whose totals you already know, and check that the answer changes correctly. A wrong column number never shows an error; only a test with known answers catches it.

4. Duplicates: the first match from the top

Type Salman in J5. There are two Salmans: roll 104 (total 195, Pass) and roll 111 (total 126, Fail). VLOOKUP returns 195, from the upper row. It never looks further down.

Classroom line

It gives only that one's answer, not the one at the bottom.

This is why real tables search by a unique column: roll number, employee ID, order ID. Names repeat; IDs don't. To get the last match instead, use XLOOKUP with search_mode -1 (next chapter). To get every match, use FILTER.

5. #N/A: not found

Type Shradha (one d missing). VLOOKUP shows #N/A: "not available", the value is not in the first column. The usual causes:

  1. A spelling difference.
  2. An extra space before or after the name ("Zoya " is not "Zoya").
  3. The number is stored as text in one place and as a number in the other.

To show a message instead of the error, wrap it in IFNA:

=IFNA(VLOOKUP(J5,$B$5:$H$15,5,FALSE),"Not found")

Duplicates and #N/A A B F 4 roll name total 5 101 Ravindra 229 6 102 Shraddha 270 7 103 Ruhi 163 8 104 Salman 195 9 105 Shahrukh 135 10 106 Zoya 241 11 107 Amir 149 12 108 Ravina 125 13 109 Raja 138 14 110 Rani 246 15 111 Salman 126 ← first Salman: returned ← second Salman: never reached =VLOOKUP("Salman",$B$5:$H$15,5,FALSE) → 195 (roll 104, first from the top) =VLOOKUP("Shradha",$B$5:$H$15,5,FALSE) → #N/A (spelling: Shraddha) =IFNA(VLOOKUP(…),"Not found") → Not found

Figure 2. Two Salmans: VLOOKUP returns the upper one (195). A misspelt name gives #N/A; IFNA turns it into "Not found".

6. Three columns with one VLOOKUP

Give the column numbers as a list in curly brackets:

=VLOOKUP(J15,$B$5:$H$15,{5,6,7},FALSE)

With Raja in J15 the formula spills into three cells: 138 | Pass | 46.00. Spilling needs Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. In older versions write one VLOOKUP per column (5, 6 and 7).

7. TRUE: approximate match, for slabs only

TRUE is not "close spelling". It means: the first column is sorted from smallest to largest, find the largest value that is less than or equal to the lookup value. That is exactly what you need for slabs such as grades, commission or delivery fees:

from % grade
0 C
60 B
75 A
90 A+

=VLOOKUP(H5,$J$19:$K$22,2,TRUE) gives Ravindra (76.33) A: 75 is the largest band start that is not above 76.33. Shraddha (90.00) gets A+, Ruhi (54.33) C, Salman (65.00) B.

Two warnings: the slab table must be sorted ascending, and on an unsorted list of names TRUE returns wrong answers without any error. For names and IDs, always FALSE.

TRUE = largest value ≤ lookup value (sorted slabs) J K 18 from % grade 19 0 C 20 60 B 21 75 A 22 90 A+ 0 C 60 B 75 A 90 A+ 76.33 → A 54.33 → C 65.00 → B 90.00 → A+ =VLOOKUP(H5,$J$19:$K$22,2,TRUE). The slab column must be sorted ascending. For names and IDs, always FALSE.

Figure 3. TRUE (approximate match) on a sorted slab table: 76.33 falls in the band that starts at 75, so the grade is A.

8. HLOOKUP and TRANSPOSE

HLOOKUP is VLOOKUP for a table that lies on its side: records go across in columns, and the headings run down the first column. HLOOKUP searches the first row and returns a value from a row below it.

Turn the table sideways with TRANSPOSE:

  1. Select a blank area 8 rows tall and 12 columns wide (the table is 12 rows × 8 columns, so the two numbers swap).
  2. Type =TRANSPOSE(A4:H15).
  3. In Excel 2021, 2024, Microsoft 365 and Excel for the web just press Enter and it spills. In older versions press Ctrl+Shift+Enter. Or copy the table and use Paste Special › Transpose, which works in every version.

Now roll numbers run along the first row, and row 7 of the new block holds the result. On the HLOOKUP sheet of the practice file the block sits in A4:L11:

=HLOOKUP(A14,$A$4:$L$11,7,FALSE)

Roll 106 → Pass (Zoya). Roll 103 → Fail (Ruhi). Roll 120 does not exist → #N/A. Row 2 instead of 7 returns the name.

TRANSPOSE, then HLOOKUP searches the first row A B C D E F G H I 4 roll 101 102 103 104 105 106 107 108 5 name Ravindra Shraddha Ruhi Salman Shahrukh Zoya Amir Ravina 6 maths 78 92 34 88 56 67 40 45 7 science 85 88 71 35 49 90 62 28 8 english 66 90 58 72 30 84 47 52 9 total 229 270 163 195 135 241 149 125 10 result Pass Pass Fail Pass Fail Pass Pass Fail 11 percentage 76.33 90.00 54.33 65.00 45.00 80.33 49.67 41.67 … to column L =TRANSPOSE(A4:H15) spills in Excel 2021 and later; older: Ctrl+Shift+Enter or Paste Special › Transpose =HLOOKUP(106,$A$4:$L$11,7,FALSE) → Pass roll 120 → #N/A; row 2 instead of 7 → the name Yellow: roll 106 found in the first row. Green: row 7 of the same column.

Figure 4. TRANSPOSE turns the 12 × 8 table into an 8 × 12 block. HLOOKUP finds roll 106 in the first row and returns row 7: Pass.

Ravindra Bagale's Tip

Use VLOOKUP when your data runs down the page (the usual case) and HLOOKUP only when it runs across. If you have a choice, keep data vertical: filters, sorting, PivotTables and every newer function work best on it.

9. The whole chapter on one page

  1. VLOOKUP searches the first column of the range and returns a column to its right.
  2. =VLOOKUP(name,$B$5:$H$15,5,FALSE): range starts at the search column; 5 = 5th column of the range.
  3. Always FALSE (exact) for names and IDs.
  4. Duplicates: the first match from the top.

  5. N/A = not found; check spelling and spaces; IFNA shows a message.

  6. {5,6,7} returns three columns (spills in Excel 2021 and later).
  7. TRUE = largest value ≤ lookup value in a sorted first column: for slabs only.
  8. HLOOKUP searches the first row; TRANSPOSE turns a table sideways (Ctrl+Shift+Enter or Paste Special › Transpose in older Excel).

10. Try at home

Try at home

Task 1: search box

  1. Name in J5, then VLOOKUPs for maths, total, result and percentage.
  2. Test with Shraddha: 92, 270, Pass, 90.00.

Task 2: traps

  1. Type Salman. Which Salman do you get?
  2. Type Zoya with a space at the end. What do you see?

Task 3: slabs

  1. Build the grade slab table and give every student a grade with TRUE. Compare with the IFS grades: which students differ, and why? (Hint: the slab table has no Fail band.)

Task 4: sideways

  1. TRANSPOSE the table, then use HLOOKUP to get the total of roll 110.

Recap

In short

  1. VLOOKUP = search down the first column, return from a column on the right.
  2. Count the column number inside your selected range.
  3. FALSE = exact match. Use it for names and IDs.
  4. First match from the top; duplicates need XLOOKUP or FILTER.

  5. N/A means not found.

  6. HLOOKUP = the same idea for tables that run across.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: VLOOKUP: exact match, column number, duplicates. Example data table with Zoya's marks: 74; Why column 3?: C = Marks; Both of Zoya's marks: 74 and 90.
Card 1 of 2: VLOOKUP: exact match, column number, duplicates · Download PNG
Revision card: VLOOKUP vs HLOOKUP. Covers Table runs; Searches; Returns from; Turn a table. With a photo of Ravindra Bagale and a short tip.
Card 2 of 2: VLOOKUP vs HLOOKUP · Download PNG

More cards for every chapter: Ravi Sir's Cards.

Loading practice tasks...

  1. VLOOKUP name (Easy): Name for ID "M103" with VLOOKUP.
  2. XLOOKUP value (Easy): The Unit cost for ID "M103".
  3. Position (Easy): Position of "Mateo Garcia" in B2:B9.
  4. INDEX and MATCH (Medium): ID for name "Mateo Garcia" using INDEX and MATCH.
  5. Not found message (Medium): Stock units for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP (Medium): VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH (Medium): Position of ID "M103" in A2:A9.
  8. Slab rate (Hard): Rate for E2 from the slab table G2:H6.
  9. Slab amount (Hard): E3 times its slab rate.
  10. Two-way lookup (Hard): Value in A2:E9 at the row of "Mateo Garcia" and column "Stock units".
  11. Lookup the top (Hard): Name with the largest Stock units.

See all practice tasks in the Task Hub →