It turns out VLOOKUP can find data even if the lookup value text and the source aren't exactly identical? For example, using 'HK ABC Ltd' can match 'ABC'?
=LOOKUP(1,0/FIND(C2:C5,F2),D2:D5)
=FIND(C2:C5,F2)
- Because FIND is wrapped by LOOKUP, Excel can evaluate C2:C5 row by row.
- That is, FIND will first evaluate =FIND(C2,F2) to find which character position C2 appears in F2.
- So it will find {#VALUE!;#VALUE!;10;#VALUE!}
=0/FIND(C2:C5,F2)
- Then divide 0 by {#VALUE!;#VALUE!;10;#VALUE!}
- Because 0 divided by any number equals 0, it will produce {#VALUE!;#VALUE!;0;#VALUE!}
=LOOKUP(1,0/FIND(C2:C5,F2),D2:D5)
- Use 1 to perform a lookup on {#VALUE!;#VALUE!;0;#VALUE!}, yielding that the 3rd item matches the condition
- Therefore extract the content from the 3rd cell of D2:D5 as the answer
Join our "Office Suite Real-time Support" to learn more about Office View details
