Using match, index to return substring

chuckf201

New Member
Joined
Nov 28, 2011
Messages
22
My spreadsheet consists of:
column A: single numbers or letters from 1-10 and a-j
column B: first-name sp last-name. (Abe Lincoln, George Washington, etc)

column f: single numbers or letters
column g: return last name only.

how can I combine both following formulas to yield the last name only in column G. (Lincoln, Washington)
=trim(RIGHT(B1,LEN(B1)SEARCH("",B1,SEARCH(" ",B11)+1)))
AND
=INDEX(B1:B11,MATCH(F1,LEFT(A1:A11,5),0),2)

Thanks

<colgroup><col width="55"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
in column G =RIGHT(B2,LEN(B2)-FIND("*",SUBSTITUTE(B2," ","*",LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))
 

chuckf201

New Member
Joined
Nov 28, 2011
Messages
22
I don't want to get the string in column B. I want to match column F with column A and return column B sub-string to column G.
in other words:
in col g: match column f with column A and return last name from column B.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Looks like...

In G1 enter and copy down:

=REPLACE(VLOOKUP(F1,A:B,2,0),1,FIND(" ",VLOOKUP(F1,A:B,2,0))-1,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,622
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top