How to do Vlookup on Excel

Question

I am new to working on Excel. I have a Table1 where It shows the product's Quantity with different dates. I need to update the Quantity from Table 1 to Table 2. Is that possible to do using Vlookup. Can someone Help me to understand how to do it.

Thanks in Advance!

Product (Table 1)	01-02-2023	01-03-2023	01-04-2023	01-05-2023
AB-01-M1	0	20	30	40
AB-02-M2	50	10	20	0

Product (Table 2)	Date	Quantity
AB-01-M1	01-02-2023	2
AB-01-M1	01-03-2023	2
AB-01-M1	01-04-2023	2
AB-01-M1	01-05-2023	2
AB-02-M2	01-02-2023	2
AB-02-M2	01-03-2023	2
AB-02-M2	01-04-2023	2
AB-02-M2	01-05-2023	2

Expected Result:

Product	Date	Quantity
AB-01-M1	01-02-2023	0
AB-01-M1	01-03-2023	20
AB-01-M1	01-04-2023	30
AB-01-M1	01-05-2023	40
AB-02-M2	01-02-2023	50
AB-02-M2	01-03-2023	10
AB-02-M2	01-04-2023	20
AB-02-M2	01-05-2023	0

yes you can try with vlookup & match, @SolarMike xlookup works in googlesheets — Γουλιέλμος, Apr 22 '23 at 22:42

Ron Rosenfeld · Answer 1 · 2023-04-22T18:55:05.293

Your Table2 is merely an unpivoted version of Table1.

In Excel 2010+ and Microsoft 365 you can create the entire Table2 give Table 1 using Power Query (built-in or available as a free MS supplied add-in in earliest versions).

Get & Transform => Get Data => From Table

Then select the Product column and Unpivot Other Columns.

In Microsoft 365, you can also create Table 2 by using a worksheet formula with some of the newer functions.

For example, entered into a single cell:

=VSTACK(
    {"Product", "Date", "Value"},
    LET(
        dataRng, CHOOSECOLS(Table1[#Data], SEQUENCE(, COLUMNS(Table1) - 1, 2)),
        rowAxis, Table1[Product],
        colAxis, CHOOSECOLS(Table1[#Headers], SEQUENCE(, COLUMNS(Table1) - 1, 2)),
        iCol, COLUMN(INDEX(rowAxis, 1, 1)),
        amountCol, TOCOL(dataRng),
        totalCells, COUNTA(amountCol),
        HSTACK(
            INDEX(
                rowAxis,
                INT(SEQUENCE(totalCells, 1, 0, 1) / COLUMNS(dataRng)) + 1,
                BYCOL(INDEX(rowAxis, 1, ), LAMBDA(aCol, COLUMN(aCol) - iCol + 1))
            ),
            INDEX(
                colAxis,
                SEQUENCE(1, ROWS(colAxis), 1, 1),
                MOD(SEQUENCE(totalCells, 1, 0, 1), COLUMNS(dataRng)) + 1
            ),
            amountCol
        )
    )
)

Creates:

from your Table 1 above

Adapted from the answer given by @pgSystemTester

score 0 · Accepted Answer · answered Apr 22 '23 at 14:46

You can use index(match) to match the dates with the product number to get the values. I took your sample and placed them side by side on a sheet and used this formula in I2 and dragged it down.

=INDEX($B$2:$E$3,MATCH(G2,$A$2:$A$3,0),MATCH(H2,B$1:E$1,0))

David Leal · Answer 3 · 2023-04-22T22:42:00.587

You can try the following:

=LET(lk, A1:E3, in, A5:C13, lkdates, TAKE(lk,1),lkProd, TAKE(lk,,1),
 data,DROP(in,1), nQty, MAP(TAKE(data,,1), INDEX(data,,2), LAMBDA(p,d,
  INDEX(FILTER(lk, lkProd=p),,XMATCH(d,lkdates)))),
 HSTACK(DROP(in,,-1), VSTACK("Quantity",nQty)))

this is another way of doing it without using array helper function such as MAP:

=LET(lk,A1:E3,in,A5:C13,lkdates,TAKE(lk,1),lkProd,TAKE(lk,,1),data,DROP(in,1),
 arr, INDEX(lk, XMATCH(TAKE(data,,1), lkProd),SEQUENCE(,COLUMNS(lk))),
 nQty, INDEX(arr,SEQUENCE(ROWS(data)),XMATCH(INDEX(data,,2), lkdates)),
 HSTACK(DROP(in,,-1), VSTACK("Quantity", nQty)))

Here is the output:

The formula only depends on two ranges: lk, in so it can be easier adjusted to your real problem. The rest of the names are deduced from them, via DROP,TAKE, and INDEX. There are multiple ways of doing it, this is just one of them. We use MAP to iterate over the data of the first two columns of data. We filter the row from lk name via FILTER by product (p), then we take the corresponding column via INDEX/XMATCH combination for a given date (d).

Γουλιέλμος · Answer 4 · 2023-04-22T22:44:06.157

Here is the formula to unpivot in Google sheets.

=VSTACK({"Product","Date","Quantity"},
ArrayFormula(SPLIT(FLATTEN(A2:A3&"♦"&B1:E1&"♦"&B2:E3),"♦")))

You can use XLOOKUP & FILTER to get the values.

=XLOOKUP(G2,$A$2:$A$3,FILTER($B$2:$E$3,$H2=$B$1:$E$1))

You can work it out more dynamic way like as below

=MAP(G2:G9,H2:H9,LAMBDA(g,h,XLOOKUP(g,$A$2:$A$3,FILTER($B$2:$E$3,h=$B$1:$E$1))))

In Excel you can use Power Query Tool to Unpivot other columns

Select Table 1
From Data Tab => Get&Transform Data => From Table/Range
Paste the M-Code in Advanced Editor from Home Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product (Table 1)", type text}, {"01/02/2023", Int64.Type}, {"01/03/2023", Int64.Type}, {"01/04/2023", Int64.Type}, {"01/05/2023", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product (Table 1)"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Product (Table 1)", "Product"}, {"Attribute", "Date"}, {"Value", "Quatity"}})
in
    #"Renamed Columns"

Click on Close & Load to get Expected Result in New Worsheet

In Excel using VLOOKUP & MATCH Function you can get the expected result

Enter the formula in cell I2 and drag down

=VLOOKUP($G2,$A$2:$E$3,MATCH($H2,$A$1:$E$1,0),0)

In Excel you can use Double XLOOKUP or XLOOKUP & FILTER

Enter the formula in cell I2 to drag down

=XLOOKUP($G2,$A$2:$A$3,XLOOKUP($H2,$B$1:$E$1,$B$2:$E$3))

=XLOOKUP($G2,$A$2:$A$3,FILTER($B$2:$E$3,$H2=$B$1:$E$1))

How to do Vlookup on Excel

4 Answers4