VLOOKUP or Mixture of Index/Match to return a value

[igotgame]

I want to provide an example of the data I have and see if you guys can help me figure this out in a formula (macros can't be used):

So if I run the VLOOKUP found in Column D right now, it returns "S" because that's the first row with the TIN 12345. Then I drag the formula down and get all of them as S.

Now, what if I wanted to put SUE in all of the rows for this TIN? How can I make it return SUE instead of S? Essentially saying "For this TIN if you find a name that's more than a length of 1, use that name instead".

This spreadsheet has thousands of rows of data. I am just trying to provide the best Middle Name possible for each TIN.

I thought about a VLOOKUP with INDEX/MATCH, but I am just a bit confused by that approach.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[igotgame]

Sorry about that, post has been updated

[Sam Capricci]

I suspect there is more to it but based on what you've posted and your workbook, this should work...
=IFERROR(IF(VLOOKUP(A2,A:B,2,FALSE)="S","SUE"),"")

[igotgame]

Well there are thousands of rows in this workbook with multiple people (TINs) and names so you have to key off of TIN first to make sure you are on the right record, but then finding the longest middle name basically for that TIN is where I am struggling.

[Sam Capricci]

Maybe you need to post a more representative sample (more than one example) and hand enter your desired results to get a better formula.
If you have a lot of repeats of the same TIN you might want to set up a table to reference.

[wk9128]

Cell D2 formula , Drag down

[AliGW]

@igotgame

Are you still using Excel 2003? If not, please update your forum profile.

[igotgame]

Maybe you need to post a more representative sample (more than one example) and hand enter your desired results to get a better formula.
If you have a lot of repeats of the same TIN you might want to set up a table to reference.

I have updated the original post with a better sample file. Let me know if that helps.

[AliGW]

Try this:

=INDEX($B$2:$B$21,MATCH(1,($A$2:$A$21=A2)*(MAX(IF($A$2:$A$21=A2,LEN($B$2:$B$21)))=LEN($B$2:$B$21)),0))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[igotgame]

I may have it..I believe this is the formula:
=INDEX(B:B,MATCH(MAX(INDEX(LEN(B:B)*(A:A=A2),)),INDEX(LEN(B:B)*(A:A=A2),),0))

Now....I need to incorporate CLOSED/OPEN if possible somehow so it only pulls the longest name if the account is OPEN basically.

EDIT: Thanks Ali...I believe I posted my solution at the same time you were posting yours.

[AliGW]

This?

=INDEX($B$2:$B$21,MATCH(1,($A$2:$A$21=A2)*($C$2:$C$21="OPEN")*(MAX(IF($A$2:$A$21=A2,IF($C$2:$C$21="OPEN",LEN($B$2:$B$21))))=LEN($B$2:$B$21)),0))

[igotgame]

Thank you very much! Works!

[AliGW]

Thank you very much! Works!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[wk9128]

Cell E2 array formula , drag down