Comparing workbooks

[zemerick]

I have a workbook with a column of IDs(C) and a column I want to collect results (H). In one workbook.

I have another workbook with those IDs(C). (but it's a much shorter list) and a column (AL) that has a number. I would like to search this workbook comparing ID's from this workbook to the first.. if it finds a match it puts the contents of that row (ex. AL6) and copy it to the (H) column of the first workbook where the row matches the ID(C) (ex H27 if that's where the matching ID is).

Sounds crazy and I've been pulling my hair out figuring it out. Let me know if you need more info.

 

[VoG]

Try VLOOKUP http://www.contextures.com/xlFunctions02.html

 

[zemerick]

I have, but all I can get it to return is the ID's. Maybe I'm not grasping the concept completely. from what I understand the workbooks have to be sorted ascending as well as the left most column has to contain my data I want returned. Well in this case my data I want returned is at column (AL). . I've tried =IF as well to avail.

 

[VoG]

If you need to lookup to the left then you need INDEX + MATCH http://www.contextures.com/xlFunctions03.html

 

[zemerick]

=INDEX(MATCH(C2,August_Attendance.xlsx!$C$2:$C$482),August_Attendance.xlsx!$AL$2)

??
REF Error I've used VLOOKUP and INDEX + MATCH. I'm not using it right or not understanding.

 

[VoG]

It would be something like

=INDEX('[August_Attendance.xlsx]Sheet1'!$AL:AL,MATCH(C2,'[August_Attendance.xlsx]Sheet1'!C:C),0))

 

[zemerick]

Thanks for the replies. .

=INDEX([August_Attendance.xlsx]Sheet1!$AL2:AL500,MATCH(C2,[August_Attendance.xlsx]Sheet1!C2:C834),0)

I changed it to this because it kept throwing an error saying that the file version didn't support that many columns.(255) now not sure what numbers that're being pull but they're not correspondant to the AL column at all.

 

[VoG]

The correct syntax is

=INDEX(Column to lookup from,MATCH(a value, column to find the match in,0))

You can use whole column ranges (as I did) without impacting performance.