Getting data from another spreadsheet based on values in 2 columns

Posted on 2013-10-24
Last Modified: 2013-10-25
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:


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

Question by:Andreamary
  • 2
  • 2
LVL 23

Expert Comment

ID: 39596921
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?

Author Comment

ID: 39597364
Sorry about that. I've attached a sample to better illustrate what I'm looking for.

LVL 23

Accepted Solution

NBVC earned 500 total points
ID: 39597436
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.

Author Closing Comment

ID: 39601445
Success...thanks so much!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question