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]

What version of Excel are you using?

 

[klatlap]

2010

 

[Peter_SSs]

2010

Would this work for you then?
For the first problem
=IFERROR(VLOOKUP("*"&"Hat"&"*",$A$1:$B$10,2,0),"")

For the second problem
=IFERROR(VLOOKUP("*"&"TopHat"&"*",$A$1:$B$10,2,0),IFERROR(VLOOKUP("*"&"Hat"&"*",$A$1:$B$10,2,0),""))

Note also that since you are putting the search value directly into the formulas, you could use
"*Hat*"
instead of
"*"&"Hat"&"*"
etc

 

[klatlap]

Sorry, maybe i should have explained it a bit better, this could have been a cell address like "*"&c1&"*" my aim is to search my list that contains for example "Hat" but my lookup value could be "thehatinthecat" once found it would display the number value in the next cell.

 

[Aladin Akyurek]

Looks like...

=LOOKUP(9.99999999999999E+307,SEARCH("hat",A2:A6),B2:B6)

 

[Peter_SSs]

Sorry, maybe i should have explained it a bit better,

It is still not entirely clear. Consider this sample data:

Excel Workbook
1	dog	1
2	cat	2
3	tophat	3
4	cow	4
5	hat	5
Sheet

<p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">VLOOKUP</p>
The below attempted formula in post #1 seemed to me to indicate that you would prefer to find TopHat and therefore return 3 but if A3 was instead "mouse" the you would like to find Hat and therefore return 5. Is that interpretation correct or incorrect?

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

this could have been a cell address like "*"&c1&"*" my aim is to search my list that contains for example "Hat" but my lookup value could be "thehatinthecat" once found it would display the number value in the next cell.

So, if your "lookup value" is "thehatinthecat" and that is located in cell C1 then perhaps this?

Excel Workbook
	A	B	C	D
1	dog	1	thehatinthecat	3
2	cat	2
3	Hat	3
4	dog	4
5	pig	5
VLOOKUP (2)

If still unresolved, please give more examples and expected results together with explanation in relation to that sample data. My signature block below has a link with suggestions for good ways to show your sample data and layout.

 

[klatlap]

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

 

[Caleeco]

Looks like...

=LOOKUP(9.99999999999999E+307,SEARCH("hat",A2:A6),B2:B6)

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

 

[snjpverma]

Caleeco, when you big a BigNum like this, the Lookup gives you the last value in the result array.