Search code examples
google-sheetsworksheet-function

How to map values between columns in worksheets

I have the following worksheets

SPEARDSHEET FILE A https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing.

SPREADSHEET FILE B

https://docs.google.com/spreadsheets/d/1jjZEXSnskjiuZQdWYqnZGn4jwZS-6RUoY8sGacZq-88/edit?usp=sharing

I want to populate the WORKSHEET B with values from WORKSHEET A that corresponding Month. Note that months with 0 values are omited thus in WORKSHEET A there is no Month 5 or Month 10 by design.

How can I map the values between WORKSHEET B and A?

UPDATE

This is the closest I've come to it which imports the rows form WORKSHEET A into WORKSHEET B and tries to match the columns with the month and then return the values. but does not map it to the appropriate column 

=ARRAYFORMULA(HLOOKUP(MATCH(INDEX(ARRAYFORMULA(IMPORTRANGE("153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ","Cash Flow!A1:H3"))),A1:L3),A2:L3,2))
Solution

  • A simple INDEX/MATCH will do:

    =IFERROR(INDEX('Cash Flow'!2:2,MATCH(A$1,'Cash Flow'!1:1,0)),0)

    Put it in the first cell and copy over.

    enter image description here

    enter image description here

    Note if your sheets are in different files replace 'Cash Flow'!2:2 and 'Cash Flow'!1:1 with:

    IMPORTRANGE("YourURLString","'Cash Flow'!2:2")

    and

    IMPORTRANGE("YourURLString","'Cash Flow'!1:1")

    respectively

    Actual formula:

    =iferror(index(IMPORTRANGE("https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing","'Cash Flow'!3:3"),match(A$1,IMPORTRANGE("https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing","'Cash Flow'!2:2"),0)),0)

    enter image description here

    BTW: HLOOKUP would do the work also:

    =IFERROR(HLOOKUP(A$1,'Cash Flow'!$1:$2,2,FALSE),0)

    enter image description here

    enter image description here

    NOTE if your sheets are in different files replace the 'Cash Flow'!$1:$2 with:

    IMPORTRANGE("YourURLString","'Cash Flow'!1:2")

    Now that you have shared the sheet:

    =IFERROR(HLOOKUP(A$1,IMPORTRANGE("https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing","'Cash Flow'!2:3"),2,FALSE),0)

    enter image description here

    Another Option; SUMIF()

    =SUMIF('Cash Flow'!1:1,A$1,'Cash Flow'!2:2)

    enter image description here

    enter image description here

    This does not work cross files in google sheets.

    My Cash Flows for reference:

    enter image description here