Double VLookup or INDEX MATCH or something else?

**carlwin** · 06-19-2010, 06:12 AM

Hi All,

I have read several posts responding to similar situations as mine however I have not been able to apply the solutions there. Feel free to point me to a post if the answer is to be found there.

Problem:

I have two workbooks, RESEARCH and DATES. I want the second workbook to be updated with information, namely dates, entered into the first workbook.

There are two adjacent corresponding cells in each workbook, eg, Row 1 in Workbook 1 corresponds to Row 3 in Workbook 2:

Workbook1

TYPE STATE CODE SHAPE DATE
TABLE NSW 101768 ROUND Mar 09

Workbook2

TYPE STATE DATE
CHAIR QLD
TABLE VIC
TABLE NSW

Now I need the date in Workbook 1 (Mar 09) to appear in the right cell in Workbook 2 (Row 3:Column 3) when it is entered into the first workbook.

Note also that the DATE column in Workbook1 is not actually Column3 but Column5, whereas the date column in Workbook 2 is as mentioned Column 3.

I already have some dates in Workbook 1 so I need to transfer them as well.

Any help would be greatly appreciated here.

Cheers,
Carl.

**Marcol** · 06-19-2010, 07:50 AM

This might be one way

in Book2 C2

Please Login or Register  to view this content.

Fill/Drag Down

Adjust Column refs to suit the size of your lookup

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

**carlwin** · 06-20-2010, 06:33 AM

A huge thank you Marcol for the help.

Unfortunately the problem is not solved as yet, but it is on its way. It appears to work for some cells, but not for others. Where it does not work the following appears to be occurring (I have attached two files to demonstrate):

At present where the criteria in Workbook2 is matched in Workbook1 then the value in the Row that the matched criteria appears in Workbook1 is entered into Workbook2.

So if 'Table' 'qld' appears in Row 3 of Workbook2 then if there is a match in Workbook1 then whatever value is in Row 3 (column E) of Workbook1 is returned. However I need the value to be that in the cell next to the matched criteria Workbook1. So if the matched criteria appears in Row 8 of Workbook 1 ('Table' 'qld') then I need the value in Row 8 Column E of Workbook1 to appear in Row 3 Column C of Workbook2, and not the value in Row 3 Column E of Workbook 1, even though that is the Row the criteria appears in Workbook2 (if that makes any sense).

Note that I have tried entering the forumla with CTRL-SHIFT-ENTER but this seems to make it worse.

Many thanks,
Carl.

**Marcol** · 06-20-2010, 08:21 AM

Maybe the simplest way is to use a helper column in both workbooks.

In Column C on both sheets, Concatenate A&B

Please Login or Register  to view this content.

Fill/Drag Down

Workbook Hera(1).xls Sheet1
ColumnsA:E sort ascending on Column C

Then in Workbook Dates(1).xls Sheet1 D2 either

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Fill/Drag Down

The helper columns(C) can then be hidden.

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

**carlwin** · 06-20-2010, 06:48 PM

Many thanks Marcol. This is a neat solution. I was hoping to avoid adding extra columns to the spreadsheet, however if this is the only way then it will have to do. I can't figure out why your original formula works for some rows and not others.

Anyway thanks again.

Cheers,
Carl.

**Marcol** · 06-20-2010, 08:00 PM

The key to it all, at least to me, lies in the sort order, most excel functions work better with sorted data.

I'm more into VBa, there are many formulae guys that can explain it better than me.

Concatenating the search criteria with multiple columns reduces the chance of error and makes a sorted list more specific.and hence a simpler formula can be used.

With only a few lines of data to work with solutions are easily found, but not correct when the data range is expanded.

Happy to have helped.