Trying to vlookup with multiple sheets and searching for text w/ criteria.

[agui23]

Hey,

So i'm going crazy here. I've watched videos, I've searched the web and this forum, and nothing is working. I am trying to do a vlookup with multiple sheets and I need to use a text criteria to lookup the results.

I created a concatenated column in both sheets and put it as the left-most column. This should serve as my identifier. I also removed duplicates on the second sheet. I am then trying to create a vlookup that will look at sheet 1 to see what classes certain students have. I want the look up to do lookups via text. For example, when it contains the words math, english, algebra, etc. I have a lot of cells that need to be sorted so I don't want to do it by hand. What are the forumlas needed for ELA Period Name, ELA Teacher First, ELA Teacher Second, etc? Thank you!!!!!!

For example:

Table 1: Roster ANET
[TABLE="width: 500"]
<tbody>[TR]
[TD]LastNumber[/TD]
[TD]First_Name[/TD]
[TD]Last_Name[/TD]
[TD]Middle_Name[/TD]
[TD]Eth[/TD]
[TD]Gender[/TD]
[TD]DOB[/TD]
[TD]Number[/TD]
[TD]Grade[/TD]
[TD]Course_Name[/TD]
[TD]Teacher First[/TD]
[TD]Teacher Last[/TD]
[/TR]
[TR]
[TD]Johnson12345[/TD]
[TD]Brad[/TD]
[TD]Johnson[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]m[/TD]
[TD]1/1/1111[/TD]
[TD]12345[/TD]
[TD]6[/TD]
[TD]ELA 6[/TD]
[TD]John[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]Luther-7890[/TD]
[TD]Lex[/TD]
[TD]Luthor[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]M[/TD]
[TD]2/2/2222[/TD]
[TD]7890[/TD]
[TD]6[/TD]
[TD]Math 7[/TD]
[TD]Al[/TD]
[TD]Gore[/TD]
[/TR]
[TR]
[TD]Johnson12345[/TD]
[TD]Brad[/TD]
[TD]Johnson[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]m[/TD]
[TD]1/1/111[/TD]
[TD]12345[/TD]
[TD]6[/TD]
[TD]Algebra 1[/TD]
[TD]Thomas[/TD]
[TD]Jefferson[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]LastNumber[/TD]
[TD]First_Name[/TD]
[TD]Last_Name[/TD]
[TD]Eth[/TD]
[TD]Gender[/TD]
[TD]DOB[/TD]
[TD]Number[/TD]
[TD]Grade[/TD]
[TD]ELA Period Name[/TD]
[TD]ELA Teacher First[/TD]
[TD]ELA Teacher Second[/TD]
[TD]Math Period 1[/TD]
[TD]Math Teacher First[/TD]
[TD]Math Teacher Second[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Johnson12345[/TD]
[TD]Brad[/TD]
[TD]Johnson[/TD]
[TD]-[/TD]
[TD]M[/TD]
[TD]1/1/1111[/TD]
[TD]12345[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[slash32487]

=VLOOKUP(A2,Sheet1!A:J,10,0)

vlookup translation. look up this cell (they have t be exactly the same no space before after and have to be the same format) , here is my table, use the data in the cell 10 rows away , 0 (the 0 has no relevance to what you need it)

 

[agui23]

All that does is return the J column in the first sheet and copy it to the appropriate column in sheet 2. I need it to look up specifically for one string of text, i.e. "ela" or "math", and return the appropriate information. Maybe I'm missing something :-/

 

[CodeNinja]

Try something like:

=IFERROR(INDEX(Sheet2!$J$2:$L$4,AGGREGATE(14,6,(ROW(Sheet2!$J$2:$J$4)-ROW(Sheet2!$J$2)+1)/((Sheet2!$A$2:$A$4=Sheet1!$A$2)*(LEFT(Sheet1!K$1,FIND(" ",Sheet1!K$1))=LEFT(Sheet2!$J$2:$J$4,FIND(" ",Sheet2!$J$2:$J$4)))),1),IF(ISERROR(FIND("First",K$1)),IF(ISERROR(FIND("Second",K$1)),1,3),2)),"")

That assumes your table is sheet2 rows 1-4...

This will look up the word ELA and return "ELA 6 for period name, John, and Smith for teacher first and last... it's ugly but the best I could do...

good luck,

CN.