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 |