finding partial text in an index match formula

Is this what you mean?

It's because there are two Julia's and it will only find the first, I missed that.
I'll take another look at it.

I think I found a better solution. It's an array formula so you have to enter it with Ctrl+Shift+Enter.

If there is a Julia Roberts and a Julia T Roberts it will only find the first of those two. I don't know if that is an issue but this updated version will take care of that too.
Formula now looking like a proper Excel array formula.

Formula:

Please Login or Register  to view this content.

I have no idea how I manage to upload the wrong file two times in a row. Oh well, that's life.

After lots of tinkering I decided to change strategy completely. I now compare the leftmost and the rightmost name in column H with columns C and B.

I don't know why it's doing that. Also I'm far away from home now, working looong days, so my time is kind of limited.
You're listed as running Excel 2007, the only thing I can offer right noe is to try it in Excel 2010 and see if it works there. There is nothing in the formula itself limiting the range to 880. As a temporary workaround you could split your data in batches of 800 but it may list the same Arr ID twice then if it finds the same name in two different batches.
Anyone else reading this post that cares to step in?

Hope it works with helper column

GETTING CLOSE - NEED A LITTE MORE HELP!

It's been a long while - but I'm finally trying to finish this. Thank you Jacc and azumi, for your help so far!

I'm (STILL) converting a database with about 8000 entries using excel to edit, merge, and alter some large tables before I import it.

Sheet one is 655 names like A1:C5. Sheet two has 5000 entries, many are repeated multiple times, like G1:K7.

Sheet one is a lookup table -Sheet 2 needs to return the Arr ID number EVERY TIME it finds a match in Sheet 1. azumi came up with a formula which works great to get arrID's- but only lists the first occurrence.

For subsequent appearances of a name, the arrID is blank. Since some names appear 15 or 20 times in the 5000 rows, I need to have the arrID # show each time the name appears, not just the first time... Below - Julia T Roberts in G6 and Jack Nicholson in G7 both should show the appropriate arrID''s. Julia Black in G3 appropriately shows nothing, since she isn't in the arrID table (thanks for the formula, azumi)

I"ve spent an hour trying to modify it to fix this - but I don't understand the formula in cells G2:G7 well enough to know how to adjust it. I assume it has something to do with the Countif function?

Here's the formula I was given:
=IFERROR(INDEX($A$2:$C$6,SMALL(IF(ISNUMBER(SEARCH(TRIM(LEFT(H2,SEARCH(" ",H2,1)-1))&" "&TRIM(RIGHT(SUBSTITUTE(H2," ",REPT(" ",LEN(H2))),LEN(H2))),TRIM($B$2:$B$6&" "&$C$2:$C$6))),ROW($A$2:$A$6)-ROW($B$1)),COUNTIF($K$2:K2,K2)),1),"")

Could someone show me how to modify this to show arrID's more than just the first time they appear.

Matching Names.jpg

Hello garyaw, long time no see!

So all the names in Sheet 1 are unique? I don't think I got that before since the old formula takes care of multiple hits in sheet 1.

In the workbook attached here the formula is a bit more simple and it does take care of multiple instances in sheet 2 instead.

Reading through the old posts there seems to be an issue with names that are similar. Would it be a good idea to have an extra column with a warning that it is a dubious match?

Surprisingly easy that conditional formula. For clarity I typically use a different font color in cells containing formula but I reckon you can handle the standard formatting.

Thanks!

I adjusted the formula to my actual situation. It's working great, but for one issue. Any row that doesn't contain an entry yields arrID of 657 (see below). There are hundreds of blank rows with 657 in them over the thousands of total rows.

The formula looks up the name in another sheet, named "tbl_Arr" (for table arrangers), which consists of A1:C651 (arrID, arr FN, arr LN) - The last entry in column A is arrID 657, which I assume is where the 6576 in cell c5 comes from.

Everything else is fine, but that. If it could be caused by me entering the array formula incorrectly, it's because I may be doing just that... (I did have the formula shown in the cell entered as an array though - whether it was right, I'm not sure)

below is a screen shot of part of the sheet. The formula shows up in the jpg, but because it's so small I pasted it just below also.

Jacc - You've been very kind already. would you be able to tell me what's causing the 657 in cell C5?

=IFERROR(INDEX(tbl_Arr!$A$2:$A$651,LARGE(IF(ISNUMBER(SEARCH(G5,TRIM(tbl_Arr!$B$2:$B$651&" "&tbl_Arr!$C$2:$C$651))),ROW(tbl_Arr!$A$2:$A$651)-ROW(tbl_Arr!$B$1)),1)),"")

Matching Names.jpg

If a search value cell is empty, the SEARCH function goes looking for nothing in all the cells and finds it included everywhere. The LARGE function picks out the largest (last) rownumber and voilá.
Here is a quick fix that should work.

Formula:

Please Login or Register  to view this content.

'''''''''''''''''

A lot shorter formula here

Formula:

Please Login or Register  to view this content.

And it's non-array.