Search code examples
excelexcel-formulaexcel-2010worksheet-function

Display data from one workbook depending on mapping in a second workbook

I have really hit a wall here, and hope someone with a bit more excel-knowledge than myself will be able to point me in the right direction. Have been stuck with this issues for quite some time now, and my Google-skills haven't been able to point me to a solution so far. I haven't been able to find it covered elsewhere, however, if it is, please point me in the direction.

I have 3 different workbooks.

  • RAW source document

RAW source document

  • Mapping document

Mapping document

  • Destination document

Destination document

I am looking to pull the data from the source document to the destination document based on the mapping defined in the mapping document.

So something like

if "Destination-document heading = mapping document destination"

lookup "mapping document source heading in RAW source document"
and "display all data for the specific heading in destination document".`

I have tried with the following, however it does not seem to provide any results for me:

=if(vlookup(destinationdocA1;mappingdocA1:B4;2;0);vlookup(mappingdocA1;RAWdocA1:B4;1;0);"")
Solution

  • Assume I have three workbooks as follows:

    Source.xlsx       - has a table of data starting in `A1` (as per screenshot)
Destination.xlsx  - has column headers starting in `A1` (as per screenshot)
Mapping.xlsx      - has mapping table with destination (but see below)

    You need to reverse the order of the columns in Mapping to allow VLOOKUP to work:

    Destination     Source
name_v          name
country_v       country
category_v      category

    In the range A2 in the Destination workbook add this formula:

    =INDIRECT(CONCATENATE("[Source.xlsx]Sheet1!", ADDRESS(ROW(),MATCH(VLOOKUP(A$1,[Mapping.xlsx]Sheet1!$A$2:$B$4,2,FALSE),[Source.xlsx]Sheet1!$A$1:$C$1,0))))

    Drag this formula across all your cells to get the results you want.

    In simple terms we are trying to find a row and column reference to the Source workbook to find the correct data value. We can then leverage ADDRESS and INDIRECT to display it in the Destination workbook.

    A worked example using you screenshots. In cell A2 we want the result LEGO:

    > Step 1 - map the column header (e.g. what does name_v correspond to?)
VLOOKUP(A$1,[Mapping.xlsx]Sheet1!$A$2:$B$4,2,FALSE) = "name"

> Step 2 - get the column number for "name" in the source workbook
MATCH("name",[Source.xlsx]Sheet1!$A$1:$C$1,0) = 1

> Step 3 - get the row number (assumes both tables in Source and Dest start on same row)
ROW() = 2

> Step 4 - put together the address reference
ADDRESS(2, 1)  // corresponds to Range("A2")

> Step 5 - finally reference the Source workbook to pull back the correct value
=INDIRECT(CONCATENATE("[Source.xlsx]Sheet1!", ADDRESS(2, 1)) = "LEGO"