Vlookup

[Ram Chandran]

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.

 

[bosco_yip]

Try to use this formula instead of yours

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

Regards

 

[ETAF]

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

 

[Ram Chandran]

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.

 

[p45cal]

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.

 

[p45cal]

BUT its not going to select 380 - not sure why ????

=VLOOKUP("*"&$H$3&"*","" & $B$3:$C$6,2,0)
should sort that, but that's only by luck; had there been say 380033 above the 380 you'd get the wrong result.

 

[Ram Chandran]

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.

 

[p45cal]

As I said:

Anyone will struggle to get the result you need with a plain formula

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?

 

[ETAF]

should sort that, but that's only by luck; had there been say 380033 above the 380 you'd get the wrong result.

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

 

[Ram Chandran]

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?

Yes I am using Office 365.

 

[bosco_yip]

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

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

 

[ETAF]

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))

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