Vlookup with non specific value

[klatlap]

Hi all.

I know i can use this formula =IF(ISNA(VLOOKUP("*"&"Hat"&"*",$A$1:$B$10,2,0)),"",VLOOKUP("*"&"Hat"&"*",$A$1:$B$10,2,0)) in the below list to find the number 3.

dog 1
cat 2
tophat 3
cow 4
pig 5

But how would i be able to find the number 3 doing this.

=IF(ISNA(VLOOKUP("*"&"TopHat"&"*",$A$1:$B$10,2,0)),"",VLOOKUP("*"&"Hat"&"*",$A$1:$B$10,2,0))

dog 1
cat 2
Hat 3
cow 4
pig 5

 

[Peter_SSs]

appreciate the help.

You are very welcome.

 

[Aladin Akyurek]

Sorry for the confusion, that formula works a treat, appreciate the help.

You are welcome.

I'm struggling to understand how this works... could anyone break it down for me?
What exactly does the '9.99999999999999E+307' achieve? I though the first argument of a LOOKUP function was the value you wish to lookup?

Regards
Caleeco

It functions as a look up value, which is expectedly greater than the max value in the reference LOOKUP is fed with... If we want to get at the last numeric value in a given one-dimensional reference, we need to run LOOKUP or kindred functions with a look up (lvalue) for which the following holds:

lvalue = MAX(reference)+delta

where delta is admissibly small.

9.99999999999999E+307 fulfills this condition and it's not an arbitrary number as it's a limit value of Excel itself.

See for more:
[1] https://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
[2] https://groups.google.com/forum/#!topic/microsoft.public.excel.worksheet.functions/WAspQoumocc
[3] https://www.mrexcel.com/forum/excel...tiple-matches-match-returned.html#post1523998

The last two include a hypothesis about why LOOKUP and kindred functions land on the last numeric value even though the reference they are fed with is unsorted in the ascending order.

 

[Caleeco]

It functions as a look up value, which is expectedly greater than the max value in the reference LOOKUP is fed with... If we want to get at the last numeric value in a given one-dimensional reference, we need to run LOOKUP or kindred functions with a look up (lvalue) for which the following holds:

lvalue = MAX(reference)+delta

where delta is admissibly small.

9.99999999999999E+307 fulfills this condition and it's not an arbitrary number as it's a limit value of Excel itself.

See for more:
[1] https://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
[2] https://groups.google.com/forum/#!topic/microsoft.public.excel.worksheet.functions/WAspQoumocc
[3] https://www.mrexcel.com/forum/excel...tiple-matches-match-returned.html#post1523998

The last two include a hypothesis about why LOOKUP and kindred functions land on the last numeric value even though the reference they are fed with is unsorted in the ascending order.

Thank you for the detailed response Aladin. I've not quite wrapped my head around it, but will read the links provided!

Kind Regards
Caleeco