Go Ad-Free on CAclubindia with Daily Mobile Updates. Subscribe Now!

Excel doubt on vlookup

06 January 2018

Dear All,*

I am attaching herewith a file where i have a problem in vlookup formula. For a particular desc 18*50 - lookup picks up the amount of 180*250. pls refer the attached file and let me know where i am going wrong and how to correct the same?

Value of 18*50 is Rs.25 value of 18*250 is 60. against 18*50 vlookup functions returns Rs.60.... Pls help.

Regards,

Sreevidhya V

Reply
Follow
Share

7 Replies

Balaji Sarangarajan (MBA & (CA Final)) (1131 Points)
Replied 06 January 2018

Hi Sreevidhya,

I dont know why it is coming like this as I am not a technical guy.

But if you try as per the below working in the attached sheet you will get the proper result.

Maybe some technical person might explain you clearly why it is happening like this.

Attached File : 25134 20180106124509 1784614 20180106122859 excel vlookup function doubt.xlsx downloaded: 170 times

Upgrade to CAclubindia Pro. Subscribe Now!

Rakesh Bandal (Self Employed and Agriculture) (1349 Points)
Replied 06 January 2018

I have also tried to find out what is problem

but excel is not responding to any adjustment, only solution I got is replace your ' * ' sign by ' X ' (i.e. by sign 18*50 to 18 X 50) for all your measurements

Sreevidhya V (405 Points)
Replied 06 January 2018

hi both thanks for the responses... i wil try these... meanwhile if there is any other solution also pls let me know...

Balaji Sarangarajan (MBA & (CA Final)) (1131 Points)
Replied 06 January 2018

Sure I will let you know if any other input

Suresh (Learning) (155 Points)
Replied 07 January 2018

Hi,

Arrange the Vlookup table in Ascending order , then you will get the correct result. If you have large set of data and find difficult to arrange the vlookup table in Ascending order please do let me know so that I could suggest other alternate.

Upgrade to CAclubindia Pro. Subscribe Now!

Manoj Garg (Support on Tally Excel Password Recovery etc.) (1987 Points)
Replied 27 July 2018

The asterisk as a wildcard (*)

The first wildcard we want to examine is the asterisk symbol. This is the most general wildcard of the group.

The asterisk wildcard can take on the value of any number of characters.

For instance, if we substitute the asterisk like ‘Sh*’, it could represent strings like ‘Sheet’, ‘Show’, ‘She’, ‘Shake’, ‘Shoes’, ‘Shirts’, and so on.

It can be placed at the beginning of a string like “*-01”. This would recognize all values that end with ‘-01’ regardless of the number of characters preceding it.

The usefulness of the asterisk as a wildcard should start to become a bit more obvious to you by now, right ?

Charan Raju (132 Points)
Replied 10 August 2018

Whenever using *, Follow this rule:

Just replace the * with ~*. You will get the correct answer.

Caution: Also use substitute function instead of find and replace box.

Why? This because whenever there is the asterisk, it tries to find the lookup value that starts with 18 and ends with 50. It does not care about the in-between letter/numbers. As 180*250 is top in a search, Lookup gives the resulting value of 180*250.

Hope this helps!