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

LastNumber	First_Name	Last_Name	Middle_Name	Eth	Gender	DOB	Number	Grade	Course_Name	Teacher First	Teacher Last
Johnson12345	Brad	Johnson		-	m	1/1/1111	12345	6	ELA 6	John	Smith
Luther-7890	Lex	Luthor		-	M	2/2/2222	7890	6	Math 7	Al	Gore
Johnson12345	Brad	Johnson		-	m	1/1/111	12345	6	Algebra 1	Thomas	Jefferson

<tbody>
</tbody>

Sheet 1:

LastNumber

First_Name

Last_Name

Eth

Gender

DOB

Number

Grade

ELA Period Name

ELA Teacher First

ELA Teacher Second

Math Period 1

Math Teacher First

Math Teacher Second

Johnson12345

Brad

Johnson

-

M

1/1/1111

12345

6

<tbody>
</tbody>

 

[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.