Excel Challaenge

[arnapsui]

Hi

I found the answer for the Excel Challenge ("Wildcard Lookup") in this forum. I still cannot understand why do they put a value "2^10" in the formula "=LOOKUP(2^10,SEARCH($D$2:$D$10,A2),$E$2:$E$10)". Can anyone explain what does it mean and why does it have that value?

Thanks

 

[Andrew Poulsom]

Isn't it 2^15? That's 32,768, which is one more than the maximum number of characters in a cell, and therefore one more than the value that can be returned by SEARCH.

 

[Jonmo1]

It's actually 2^15
2 to the 15th power = 32768

So the formula is essentially
=LOOKUP(32768,SEARCH($D$2:$D$10,A2),$E$2:$E$10)

Search returns a number based on where the value is found within the cell.
Example,
=SEARCH("Jon","My name is Jon")
Searches for Jon within "My name is Jon"
so Jon is at the 12th position in the string, so search returns 12.

The maximum length of a string in a cell is 32767

So using 32768 in the lookup ensures that the lookup value is larger than any possible result of the search.
so it finds the LAST numeric result of the search.