Getting data from another spreadsheet based on values in 2 columns

I am currently using this formula below to find the "last match" in the Cartography_12DEC2013_Oct21_Test.xlsm spreadsheet and populate Column N in the Book Production spreadsheet:

=IFERROR(LOOKUP(2,1/('[Cartography_12DEC2013_Oct21_Test.xlsm]Cartography'!$H$2:$H$675=M2),'[Cartography_12DEC2013_Oct21_Test.xlsm]Cartography'!$I$2:$I$675),"")

I am hoping to be able to modify this formula as follows:
1. Lookup $H$2:$H$675  on Cartography spreadsheet to find match(es) for value in column “M” in Book Production spreadsheet.
2. Then within the result from step 1, look up most recent initials/date stamp ( format is "RS 13 Oct 13") in column $AW$2:$AW$675 in Cartography spreadsheet.
3. From the row identified by Step 2, insert the value from Cartography $H$2:$H$675 column into column "N" in the Book Production spreadsheet.

I hope I've been able to express this clearly enough...don't hesitate to let me know if you have any questions...

Thanks!
Andrea
AndreamaryAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NBVCConnect With a Mentor Commented:
Try this formula:

=INDEX('Cartography Results'!$F$2:$F$15,MATCH(1,('Cartography Results'!$E$2:$E$15=A2)*(IFERROR(MID('Cartography Results'!$G$2:$G$15,4,255)+0,0)=MAX(IF('Cartography Results'!$E$2:$E$15=A2,IFERROR(MID('Cartography Results'!$G$2:$G$15,4,255)+0,0)))),0))

Open in new window


confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.
0
 
NBVCCommented:
The details are a little confusing.  Can you explain what the difference would be from the formula you already have (and just changing the $I$2:$I$675 to $AW$2:$AW$675?
0
 
AndreamaryAuthor Commented:
Sorry about that. I've attached a sample to better illustrate what I'm looking for.

Thanks,
Andrea
EE-Andreamary-Oct24.xlsm
0
 
AndreamaryAuthor Commented:
Success...thanks so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.