CODEWITHSUNDEEP
excelvlookuplookup
cyborg
cyborg

Reputation: 491

Lookup vs Vlookup, the called value is a mismatch against the correct value

I have a movies csv file, where one worksheet has the details of the movie and the other worksheet has the budget and gross information of the movies.

All the movies have a unique ID and using that ID I am trying to pull the budget and gross information into the worksheet 1, which has all the details about the movie.

Worksheet 1 - Lookup Function

A5826 has the unique ID of a movie, when I hit enter the value I get in the budget field of worksheet 1 in different from the actual value present in the worksheet2, which can be seen in the below screenshot.

Worksheet 2 - Actual Value

I changed the format of the budget column from Currency to Number, thinking that might be causing the issue but it was of no help.

I am unable to understand which value is the lookup function picking from the worksheet 2.

I also tried with vlookup function,

Worksheet 1 - Vlookup function

But this is resulting in an error and I am unable to figure out whats the issue with the formula, everything seems to be correct here.

Can anyone point out what could be the issue I am facing?

I just checked to see what value was the lookup function was returning from the search, it is returning some other ID value and for the ID value the budget is 32000, I am not sure why lookup is doing that.

Value Returned

Upvotes: 0

Views: 89

Answers (1)

TomJohn
TomJohn

Reputation: 747

Firstly, two remarks regarding LOOKUP function (https://support.office.com/en-us/article/LOOKUP-function-446d94af-663b-451d-8251-369d5e3864cb):

  • If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

  • If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.

Additionaly:

“LOOKUP requires that the first column of the vector (or the first column or row for the array form) is sorted in ascending order. The following information describes different formulas that you can use to return the same information returned by LOOKUP without requiring that the first column of the table be sorted. “ (https://support.microsoft.com/en-us/help/181212/how-to-use-the-lookup-function-with-unsorted-data-in-excel)

This is probably what is happening in your case. Are you sure that if you try 

=P5826=budget_gross.tsv!A5435

it will return TRUE?

Secondly, you are not using VLOOKUP function appropriately. Please refer to some tutorial on that function (https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1). If you are sure that above example returns true, you may try INDEX/MATCH combo.

=INDEX(budget_gross.tsv!B$2:B$8468,MATCH(A5826,budget_gross.tsv!A$2:A$8468,0),1)

First argument of INDEX function is a range of your data/results, second argument is number of row - in this case we do not know it so we use MATCH function to find it, last argument is no of column which is 1 in our case, becouse we have 1 column range .

Upvotes: 2

Related Questions