• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup

R

Ram Chandran

Member
Hi Excel Gurus,

I have attached Sample worksheet. I have doubt in Vlookup function.

The lookup value inside multiple values in a cell means how can i put vlookup function.

Kindly help me this.

Thanks in advance.
 

Attachments

Hi Bosco_yip,

Thank you for kind response.

in this formula if i put single value i.e., (before and after no value) means it cannot pickup the return value.
And also i have given that range between numbers i.e., (38096-38098) i want lookup value for 38097 also.
Kindly help to resolve.


Thanks in advance.
 
Anyone will struggle to get the result you need with a plain formula, especially when you have ranges of numbers like 38096-38098 where I presume you're going to want 38097 to be 'found', even more complex when you have multiuple such range representations in one cell.

In the attached, is a Power Query table which tries to expand your table into single number/Order pairs against which you can do a plain VLOOKUP.
That table is at cell L2; should you change the contents of your table at cell B2, you will need to right-click the L2 table and choose Refresh to update the results. Formula at cell I3 now refers to that table.

Requirents:
Numbers in the Numbers columns have to be whole numbers
When there is a range denoted by the hyphen, the smaller number should be first.

81955
 

Attachments

Hi p45cal,

Thank you for your immediate response and it is very useful for me.

Is that any way to formulate without creating any table.
Thanks in advance.
 
As I said:
p45cal said:
Anyone will struggle to get the result you need with a plain formula
Click to expand...
Merely making this statement might encourage someone to produce one...
I'm not going to try any time soon.
It would improve your chances a lot if you can tell us that your version of Excel is Office 365. Can you?
 
should sort that, but that's only by luck; had there been say 380033 above the 380 you'd get the wrong result.
Click to expand...
i could not get the formula to select the 1st entry which is 380 on its own , it choses the first "text" i did play with various options.

Anyway - if works on the OP version , i guess thats fine - i just could not get it to to choose the 1st entry of 380 on its own - even if formatted as text or number
 
p45cal said:
As I said:
Merely making this statement might encourage someone to produce one...
I'm not going to try any time soon.
It would improve your chances a lot if you can tell us that your version of Excel is Office 365. Can you?
Click to expand...


Yes I am using Office 365.
 
ETAF said:
try
=VLOOKUP("*"&$H$3&"*",$B$3:$C$6,2,0)

BUT its not going to select 380 - not sure why ????
index/match not working
need to look into futher
bosco_yip may know
Click to expand...

To select & test for 380, you need this:

=IFERROR(VLOOKUP($H$3,$B$3:$C$6,2,0),VLOOKUP("*"&$H$3&"*",$B$3:$C$6,2,0))

Regards
 
To select & test for 380, you need this: =IFERROR(VLOOKUP($H$3,$B$3:$C$6,2,0),VLOOKUP("*"&$H$3&"*",$B$3:$C$6,2,0))
Click to expand...
I must just have been lucky in the past that the string on its own never existing in my data - Or i just did not notice ...... interesting
Thanks for that
 
You must log in or register to reply here.
Back
Top