w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Using substrings within an INDEX MATCH fucntion

I think you're on the right track. I think your initial formula should work but you need to make two changes:

  1. Match criteria should be "TRUE" instead of "B3", as the result iof the ISNUMBER() function is a TRUE or FALSE
  2. When entering the formula, press CTRL+SHIFT+ENTER instead of just ENTER, to make this an array function

Formula:

=INDEX(Sheet2!G2:Sheet2!G2340,MATCH(TRUE,ISNUMBER(SEARCH(B3,Sheet2!C2:Sheet2!C2340)),0))

If you wanted to search for both the first and last name, you could multiply two ISNUMBER() arrays together, and search for 1 instead of TRUE. This would get it to match both A3 and B3:

=INDEX(Sheet2!G2:Sheet2!G2340,MATCH(1,ISNUMBER(SEARCH(A3,Sheet2!C2:Sheet2!C2340))*ISNUMBER(SEARCH(B3,Sheet2!C2:Sheet2!C2340)),0))

Alternately, you could attempt to match both first and last names together with:

=INDEX(Sheet2!G2:Sheet2!G2340,MATCH(TRUE,ISNUMBER(SEARCH(B3&",
"&A3,Sheet2!C2:Sheet2!C2340)),0))

Hope this helps. If I missed something, let me know!





© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.