CODEWITHSUNDEEP
excelexcel-formulavlookup
SMH
SMH

Reputation: 1316

VLOOKUP is not matching properly

I have a multisheet excel file where I am in sheet3 and trying to find the data for a person in sheet2 but it doesn't match correctly, it gets the data of another person. So I am trying to get the number of days for "Tim" from sheet2:

enter image description here

but is shows like that:

enter image description here

The code is I used was =VLOOKUP($B81,sheet2!$A$2:$M$281,2)

Upvotes: 1

Views: 462

Answers (2)

Terry W
Terry W

Reputation: 3257

try 

=VLOOKUP($B81,sheet2!$A$2:$M$281,2,FALSE)

or 

=VLOOKUP($B81,sheet2!$A$2:$M$281,2,0)

if you want exact match.

The last argument of the VLOOKUP function determines the nature of your look up.

1 - TRUE will return an approximate match which is useful when you want to look up a value from group of ranges in ascending order;

0 - FALSE will return an exact match which will return the first match based on your look up criteria.

If you do not input the last argument in your formula, Excel will presume you want to use 1 - TRUE and do an approximate match which will look up the closest (and smaller) match of Tim such as Sam which is considered ahead of Tim and return the value corresponding to Sam instead. However, if your formula is =VLOOKUP($B81,sheet2!$A$2:$M$281,2,) with a comma , at the end of 2, Excel will presume you want to use 0 - FALSE and do an exact match.

Upvotes: 3

Regiz
Regiz

Reputation: 467

Terry W already answered about the exact match, however here you go for some more trail which helps you to work with v-look up easier way.

Table Of RAW Data

enter image description here

Table Of Sheet 2, where we need values of TIM

Trail 1 (Used For Jan):

=VLOOKUP(B4,Raw!A1:M4,2)

Which is worked without using exact match function, as you tired.

Trail 2 (Used For Jan):

 VLOOKUP(A4,Raw!A1:M4,2,0)

Which is worked using exact match function as "0", as you tired.

Trail 3 (Used For Jan):

 =VLOOKUP($B10,Raw!$A$1:$M$4,MATCH(C$9,Raw!$A$1:$M$1,0),0)

Here instead of using the Index_Num Used match function and used Exact Match Argument too. The match function helps to get exact value of the month, and which make work easier instead of mentioning the Index_Num as 1 and 2 and 3 and Ext... for next and next months. Below snap is for your view.

enter image description here

Please let me know if need any more clarification need on this.

Upvotes: 2

Related Questions