Google-sheets – Copying data, based on the value in a cell

google sheets

I've been trying to work out how to copy data from cells on a worksheet into another based upon the value in a cell.

We have weekly data so for arguments sake

Week 1     Week 2     Week 3     Week 4

   1         5          3          2

  $10       $90        $75        $15

What I'd like to be able to do, is input the overall worksheet with Week 1 and it automatically drags 1 and 10 into cells from the data worksheet.

I've been trying to make lookup address & vlookup work, but neither appear to do what I'd like them to… I'm using Google Docs if that makes any difference?

Best Answer

If Week 1 is in A1 of both sheets, 1 is in A2 of a worksheet etc then this might suit in B1 of another:

 =index(a!$A:$D,2,match($A1,a!1:1,0))

copied across to C1 and the 2 there replaced by 3.

Related Solutions

Querying and Sorting Data from Multiple Sheets in Google Sheets

To sort a query you simply have to wrap it in SORT.

=SORT(
    QUERY(
        {SheetOne!C3:F;SheetTwo!C3:F;SheetThree!F4:G;SheetFour!F4:G}
    )
    , 1, TRUE)

For example, the above formula takes the data from the sheets inside the query, and then sorts by ascending order from column 1.

This also works well to solve the problem of row splitting, where some rows get split up if you use different queries to pull from rows you intend to keep uniform.

Google-sheets – Return cell reference containing closest value

=VLOOKUP (a1,range,2) 
The problem with that is if the value isn't listed, which most of the time it isn't, it will fail. I want to return the closest value, which VLOOKUP doesn't appear to do

First, sort range in ascending order. You could do in place or use SORT

Try

=VLOOKUP(a1,SORT(range,1,TRUE),2)

Best Answer

Related Solutions

Querying and Sorting Data from Multiple Sheets in Google Sheets

Google-sheets – Return cell reference containing closest value

Related Topic