# Excel question: double lookup? **URL:** \ **Category:** Factual Questions\ **Created:** [May 16, 2005, 9:54pm UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142 "2005-05-16T21:54:21Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Rattlehead02](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rattlehead02/32/3944_2.png) [@Rattlehead02](https://boards.straightdope.com/u/Rattlehead02)\ **Post date:** [May 16, 2005, 9:54pm UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142/1 "2005-05-16T21:54:21Z")
Here’s the deal: I have 2 workbooks. One is basically a template (we’ll call it “main”), the other is replaced monthly with updated data (we’ll call it “data”). In main, I have a sheet with a row for political jurisdictions, and other rows for total building permits pulled, average cost listed on the permits, etc. I have one of these sheets for each county we deal with (4). The other book, Data, has one sheet and is basically the exact same as the sheet I just described in main, with one difference: the jurisdictions are in a different order. Also, all 4 counties are in 1 sheet in data. it has a column to indicate the county. all i need to do is find the jurisdiction from main in data and plug in number of permits, average cost, etc back into main. This wouldn’t be a problem if there weren’t the same jurisdiction name in more than 1 county. So, i need a formula that will look at county AND jurisdiction, then get the corresponding offset cell data. Any thoughts? I could do this fairly easily in VBA, but I’d much rather have it as a formula. Thanks, and sorry for being so long winded 🙂 Jeff ---
**Author:** ![Gangster\_Octopus](https://avatars.discourse-cdn.com/v4/letter/g/a8b319/32.png) [@Gangster\_Octopus](https://boards.straightdope.com/u/Gangster_Octopus)\ **Post date:** [May 16, 2005, 11:16pm UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142/2 "2005-05-16T23:16:32Z")
The easiest thing to fo, I would htink would be to create a unique identifier for each rcord compoed of something like the first five letters of the county and the district name. So a county WILLIAMS and a district 5 then the identifier would be WILLI5. In the main you would then use the LEFT comand in the lookup function. ---
**Author:** ![nivlac](https://avatars.discourse-cdn.com/v4/letter/n/3bc359/32.png) [@nivlac](https://boards.straightdope.com/u/nivlac)\ **Post date:** [May 17, 2005, 1:47am UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142/3 "2005-05-17T01:47:21Z")
**GildedLily** , Have you tried the “match” function? ---
**Author:** ![RealTronic](https://avatars.discourse-cdn.com/v4/letter/r/9dc877/32.png) [@RealTronic](https://boards.straightdope.com/u/RealTronic)\ **Post date:** [May 17, 2005, 6:16am UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142/4 "2005-05-17T06:16:10Z")
> [@Gangster Octopus](#): > > The easiest thing to fo, I would htink would be to create a unique identifier for each rcord compoed of something like the first five letters of the county and the district name. So a county WILLIAMS and a district 5 then the identifier would be WILLI5. In the main you would then use the LEFT comand in the lookup function. That is what I would do also. Just add a new column to the data sheet which combines county and district names. Some examples: > [@](#): > > = A1 & B1 (would combine all data in the cells to a unique combo-value) > = LEFT(A1,4) & LEFT(B1,4) (would combine the first 4 characters in each cell) > etc. etc. Of course, then in the VLOOKUP formula, you’d have to manipulate the search criteria the same way to get a unique match. I’m not familiar enough with the MATCH formula to discuss it - but I think that it’s not enough to use it on its own to get want you want - you could probably combine it for a complex lookup function. ---
**Author:** ![Bottle\_of\_Smoke](https://avatars.discourse-cdn.com/v4/letter/b/b9e5f3/32.png) [@Bottle\_of\_Smoke](https://boards.straightdope.com/u/Bottle_of_Smoke)\ **Post date:** [May 17, 2005, 3:17pm UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142/5 "2005-05-17T15:17:53Z")
I think this might give you what you need in one formula without having to add a column to your data spreadsheet. It is pretty much the same idea mentioned above but the concatenation occurs in the formula so you don’t need a seperate column. Using named ranges will make this clearer in my head, so assume on your main sheet you have a range for county (“main\_cty”) and jurisdiction (“main\_jur”) and on your data sheet you also have a range for county (“data\_cty”) and jurisdiction (“data\_jur”) as well as ranges for each of the columns you want to reference (“data\_permits” for example). Then: {=INDEX(data\_permits,MATCH(main\_cty&main\_jur,data\_cty&data\_jur,0))} will give you the data in the “permits” column where the combination of the county/jurisdiction on the data sheet matches the combination of county/jurisdiction on the main sheet. Just keep in mind that this is an array formula so you need to Ctrl+Shift+Enter to get the {} brackets. ---
**Author:** ![Bottle\_of\_Smoke](https://avatars.discourse-cdn.com/v4/letter/b/b9e5f3/32.png) [@Bottle\_of\_Smoke](https://boards.straightdope.com/u/Bottle_of_Smoke)\ **Post date:** [May 17, 2005, 3:22pm UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142/6 "2005-05-17T15:22:03Z")
Whoops. I screwed that up. You don’t want to use the whole range on the main sheet in the formula. You just want to use the combination of two specific cells. So: {=INDEX(data\_permits,MATCH(A1&A2,data\_cty&data\_jur,0))} where A1 is your county on the main sheet and A2 is the jusrisdiction (or whatever the actual cells will be). ---
**Author:** ![Gangster\_Octopus](https://avatars.discourse-cdn.com/v4/letter/g/a8b319/32.png) [@Gangster\_Octopus](https://boards.straightdope.com/u/Gangster_Octopus)\ **Post date:** [May 17, 2005, 5:45pm UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142/7 "2005-05-17T17:45:47Z")
**Bottle of Smoke** I am not sure that will work. If the data looks something like: CTY, District ABLE, 1 ABLE, 2 ABLE, 3 BROOM, 1 BROOM, 2 BROOM, 3 etc. then defining the ranges would be tricky as you suggest. ---
**Author:** ![Rattlehead02](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rattlehead02/32/3944_2.png) [@Rattlehead02](https://boards.straightdope.com/u/Rattlehead02)\ **Post date:** [May 17, 2005, 6:23pm UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142/8 "2005-05-17T18:23:52Z")
Well, I decided to go about this a different route, though I do appreaciate all the help. But now I have another issue. I’m running Windows XP Pro and Excel 2003 I have the formula my Main book: =SUMIF(‘H:\Data\Share\Build\_Fax\Geo\_Summary[thisyear.xls]thisyear’!$E$7:$E$32,$A9,‘H:\Data\Share\Build\_Fax\Geo\_Summary[thisyear.xls]thisyear’!F$7:F$32) and it returns: #VALUE! unless i change it to: =SUMIF(thisyear.xls!$E$7:$E$32,$A9,thisyear.xls!F$7:F$32) For some reason it doesn’t like the directory being included, which wouldn’t be a problem, but Excel put the directory info there itself!! The ThisYear book is created in Foxpro and opened in VBA (via the Main workbook). Initially I had: =SUMIF(thisyear.xls!$E$7:$E$32,$A9,thisyear.xls!F$7:F$32) as my formula and it worked great, but the next time I started from scratch, had Main open ThisYear and all of a sudden there is directory info in my formula. What can I do about that? Thanks, Jeff ---
**Author:** ![RealTronic](https://avatars.discourse-cdn.com/v4/letter/r/9dc877/32.png) [@RealTronic](https://boards.straightdope.com/u/RealTronic)\ **Post date:** [May 18, 2005, 5:55am UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142/9 "2005-05-18T05:55:46Z")
I think the problem is that it’s trying to read the data from a worksheet called ‘thisyear’ - is the sheet you’re pulling data from really called that? Otherwise you have to change ‘thisyear’ that shows up after the filename in brackets to reflect the actual sheet-name. ---
**Author:** ![Rattlehead02](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rattlehead02/32/3944_2.png) [@Rattlehead02](https://boards.straightdope.com/u/Rattlehead02)\ **Post date:** [May 18, 2005, 3:41pm UTC](https://boards.straightdope.com/t/excel-question-double-lookup/304142/10 "2005-05-18T15:41:12Z")
Yes, the sheet’s name is “thisyear”. When a workbook is created from within Visual Foxpro it automatically names the sheet whatever you name the book.