Lookup On Portion Of Search Value

[masouder]

I am trying to perform a lookup where the set of numbers being searched contain a portion of the value that I am looking for. Please see the example below where I am trying to find 701 in B3:B6. I want to return 70. My first thought was to try the VLOOKUP function, but perhaps a different function (MACTH?) would work. I recognize that I could use the LEFT function to isolate 70 within 701, but the 701 value that I am looking for could be 7011, 70111, et cetera. Any help appreciated.

Book1
	A	B	C	D	E	F
1
2
3		60		Value	701
4		70		Lookup
5		80
6		90
7
8
9
Sheet1

 

[Peter_SSs]

Does this do what you want?
(In your version you may need to confirm the formula with Ctrl+Shift+Enter, not just Enter)

23 01 15.xlsm
	A	B	C	D	E
1
2
3		60		Value	701
4		70		Lookup	70
5		80
6		90
Lookup

Cell Formulas
Range	Formula
E4	E4	=INDEX(B3:B6,MATCH(1,FIND(B3:B6,E3),0))

 

[Peter_SSs]

(In your version you may need to confirm the formula with Ctrl+Shift+Enter, not just Enter)

If that is the case and you would prefer not to have to do that, you could test this one instead.

23 01 15.xlsm
	A	B	C	D	E
1
2
3		60		Value	701
4		70		Lookup	70
5		80
6		90
Lookup

Cell Formulas
Range	Formula
E4	E4	=AGGREGATE(15,6,B3:B6/(FIND(B3:B6,E3)=1),1)

 

[masouder]

These both appear to work well. Thank you both.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.