I would like to search an array for a cell in the function's row and return the cell adjacent to the matching array row. The array cells contain partial matches with the function's row.
| clients | return |
|---|---|
| bob smith | ***!!! |
| kim lee | |
| joe blow | |
| frank tank |
| array | value |
|---|---|
| joe | 15 |
| bob | 21 |
| frank | 33 |
| kim | 7 |
=(INDEX($A$8:$A$11,MATCH(1,ISNUMBER(SEARCH($A$8:$A$11,A2))*1,0)))
This functions returns the matching array cell but not the value adjacent to the marching array cell. I want the numbers; not the names.
You may try the following formula-
=FILTER($G$2:$G$5,NOT(ISERROR(XMATCH($F$2:$F$5,TEXTSPLIT(A2,," ")))))
If you want only first name match, then could try-
=XLOOKUP(TEXTSPLIT(A2:A5," "),F2:F5,G2:G5)
This formula looks for the first word (separated by space) of the client name and return the number corresponding in the second table.
=SUM(IF(LEFT(A2,SEARCH(" ",A2)-1)=A$8:A$11,B$8:B$11,""))
The formula is placed in cell B2 and drag down
| A | B |
|---|---|
| clients | return |
| bob smith | 21 'formula is here |
| kim lee | 7 |
| joe blow | 15 |
| frank tank | 33 |
| array | value |
| joe | 15 |
| bob | 21 |
| frank | 33 |
| kim | 7 |
=(INDEX($B$8:$B$11,MATCH(1,ISNUMBER(SEARCH($A$8:$A$11,A2))*1,0)))kimit would match onkimberlyfor instance, or evenRakim.=IFNA(INDEX($B$8:$B$11,MATCH(1,--ISNUMBER(SEARCH("|"&$A$8:$A$11&"|","|"&SUBSTITUTE($A2," ","|")&"|")),0)),"").