Search help

[demonfootball21]

I need to look up results by one column, but if that column has multiple matches I want it to move to another column.

example - I am using Last Names to find their personal info, but if more than one person has the same last name i need it to check their first name.

the formula I have used now is =VLOOKUP($A2,Students!$A$2:M1599, COLUMNS(Students!$N4:N4)+12,0)

so if I have
Smith, Jon and
Smith, Bob
it only brings up Jon's corresponding info, not Bobs.

Thanks!

 

[jmthompson]

If you want to use Vlookup, your best bet is to add a helper column to your source sheet which concatenates the last and first names. Then use

=vlookup(concatenate(a2,", ",b2),Students!$A$2:M1599, COLUMNS(Students!$N4:N4)+12,0)

Else use Index/Match

 

[demonfootball21]

Is there another forumla that would work best in this case? I just used Vlookup cause i was fimilar with it!

Thanks!

 

[jmthompson]

Last name in A, first name in B on both sheets?

What column contains the data you want to return and how many rows are in that sheet?

 

[demonfootball21]

I have my source sheet(Students!) that has Every Kids name and their info starting with A(last) B(First)

Then i have another sheet that is an exact copy of my Students! Sheet(called Final!). The Final! Sheet has all my formulas

In my third sheet(Failing_Kids!) I get a report of failing kids starting with column A-Last B-First. Once I copy the names into Failing_Kids! The names are pulled into the Final Sheet. In my final! sheet Columns C through AX match the first and last name to the Students! sheet that has their info.

So basically the Kids Names are pulled from the Failing List!, and their info is pulled from the Student! sheet. Everything works perfect till I get a kid with the same last name and it always pulls the first kid. I am not able to add any other columns as they match up with the other program

The number of rows vary each week but last week was about 390.