index/match

Stephen Hindes

sheet 1 has a name i am looking for i have 2 sheets (only has part of it)

i need to past in sheet one the age.

i have tryied using it Index/Match, but it only looks for exact match.

i have tries using collect, and/or contains and could not do it.

can someone help my in string to pull data from sheet 2 if i only have partial info?

Nik Fuentes

hmm...

The way I see it if the two name columns were switched, and Sheet 2 had the full name while sheet 1 had the abbreviated name, that would make things doable. that way you could do a vlookup()/Index(Match()) with the Contains() in the search key. is switching them an option? I presume not.

As it is now, is it always going to be that the Sheet 1 Name is First and Last, while the Sheet 2 Name is just first? If so, you could use a Find() function to locate the space [" "] in the Sheet 1 Name, then use a Left() function to isolate the first name. That would look like Left(Name@row, Find(" ", Name@row)-1) [in the john doe example, the Find() function would return position 5, so the Left() function would be Left(Name@row, 5-1), which would return the first 4 characters (john)]. This would work regardless of the length of the first name, but it would only work if the two name columns are consistent. If you want the Last Name instead, you would use the Right() function, additionally having to account for the length of the string using the Len() function [Right(Name@row, Len(Name@row)-Find(" ", Name@row))].

The suggestion above will get you a search key for a vlookup() or Index(Match()) that will be a compatible exact match. I still thank that the easiest option would be updating the procedure for Sheet 2 to have it be the full name/string, but presuming that this is all placeholder information and you're operating in a system that you don't have the liberty to change, this should work. Does this help? Let me know if you need any further help :]