Name search formula within text then sumifs from sheet2 to sheet1

**Shruder** · 12-28-2018, 11:40 AM

Hello,

I am looking to use a search formula for first, last name then sumif between two tabs

Sheet1: Search for below name in column text

Sheet1.PNG

Sheet2: Search same name, and if there is a match then sum the totals per that condition

Sheet2.PNG

So Essentially, if the search can match the names exact, then sum the totals in a new column on Sheet 1.

I have been trying the IF search then sumifs it agrees to sheet 2 search results. I do not have a name list that I can use so not sure how the search will work.

I really just want to find a match each time those columns from both sheets find a name match and then sumif the totals per that match from sheet to to a new column in Sheet1.

I do not want to use text to columns at this point.

Any thoughts on formula solutions?

Please Login or Register  to view this content.

**Pepe Le Mokko** · 12-28-2018, 11:47 AM

Will the full name always be in last and second last position?

**FlameRetired** · 12-28-2018, 06:13 PM

If you do not want to use Text to Columns are you OK with a helper column?

In the attached column D 'Sheet2' this normalizes the names. It someone may be able to shorten it.

Formula:

Please Login or Register  to view this content.

then in D2 'Sheet1'

Formula:

Please Login or Register  to view this content.

Edit Here is a shorter helper formula column D 'Sheet2'.

Formula:

Please Login or Register  to view this content.

**Bo_Ry** · 12-29-2018, 12:15 AM

Please try D2

=SUMIFS(Sheet2!$C:$C,Sheet2!$B:$B,TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",20)),20))&", "&TRIM(LEFT(RIGHT(SUBSTITUTE(C2," ",REPT(" ",20)),40),20))&"*")

**Shruder** · 01-07-2019, 06:43 PM

@Pepe Le Mokko

to reply to your post question: Will the full name always be in last and second last position?

Shruder Reply: No, it will be a variable potentially each cell.

Name search formula within text then sumifs from sheet2 to sheet1

LinkBack

Thread Tools

Rate This Thread

Display

Name search formula within text then sumifs from sheet2 to sheet1

Re: Name search formula within text then sumifs from sheet2 to sheet1

Re: Name search formula within text then sumifs from sheet2 to sheet1

Re: Name search formula within text then sumifs from sheet2 to sheet1

Re: Name search formula within text then sumifs from sheet2 to sheet1

Thread Information

Users Browsing this Thread

Similar Threads

Help: VBA to search in sheet1, match data in sheet2, return search results back in sheet1

if cells in Column A sheet1 and Column A sheet2 match, return text from same Sheet1 row

match data of sheet1 and Sheet2 ColumnA, if matched copy columnB of sheet1 to sheet2

[SOLVED] Formula for finding text in sheet1 with numbers from sheet2

To search for value from sheet1 in a range on sheet2 and copy same row different column

[SOLVED] Copy and Paste Entire Row from Sheet1->Sheet2 based on text string match in Sheet1 Row

[SOLVED] How to search and add a value from sheet1 to sheet2

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions