How to match two columns with Excel?

Question

I have a table like this:

+----------+---+---+----+
|   date   | A | B | C  |
+----------+---+---+----+
| 3-Sep-18 | 3 | 7 | 11 |
| 4-Sep-18 | 4 | 8 | 12 |
| 5-Sep-18 | 5 | 9 | 13 |
+----------+---+---+----+

How to get value from above match column act and date, and get the below table on the same sheet?

+-----+----------+-------+
| act |   date   | value |
+-----+----------+-------+
| A   | 5-Sep-18 |     5 |
| B   | 5-Sep-18 |     9 |
| C   | 5-Sep-18 |    13 |
+-----+----------+-------+

you need to un-pivot your data first, then it'll be easy with a pivot table. — Máté Juhász, Sep 10 '18 at 09:25
why don't just use 2 match functions on those 2 columns on the output table? — phuclv, Sep 10 '18 at 09:28
Ok. So what spreadsheet columns and rows are the tables on? For example, is the second table on cells `A6` to `C9` or `F1` to `H4`? And importantly, is the first table `A1` to `D4`? This will help with providing working formulae. — Chris Rogers, Sep 10 '18 at 09:29
@ChrisRogers you can test as you wish what the cells are,thanks — J.Cooker, Sep 10 '18 at 09:31
@phuclv I am new to excel,however,our environment only has excel..please show me the code..thanks — J.Cooker, Sep 10 '18 at 09:32
"please show me the code..thanks" - this is not the right approach here. We help each other to learn, but don't do the others' job. "pls show me the code" clearly shows you just want somebody to do it for you. — Máté Juhász, Sep 10 '18 at 11:09
@ChrisRogers "And importantly, is the first table A1 to D4?" - that's irrelevant here. If you wish to provide a solution based on formulas, just do it, and attach a screenshot too, so it'll be clear to what cells your formulas refer to. — Máté Juhász, Sep 10 '18 at 11:11

score 0 · Accepted Answer · edited Sep 10 '18 at 14:17

Lets assume that the first table is in Sheet1 and the desired output is in Sheet2

Also, on Sheet1, the header is on the first line.

This way, on Sheet2, the header would be also on the first line, but most important, the first column of data is on Column A, the second on Column B, so you would need to put the following formula on Column C:

=INDEX(Sheet1!A:D;MATCH(Sheet2!B2;Sheet1!A:A;0);MATCH(A2;Sheet1!1:1;0))

It is important to notice here:

Sheet1!A:D should go from the first column to the last you have data on it. If you have information, lets say, until column ZZ, it would need to be Sheet1!A:ZZ
If the header isn't on the first line of the sheet, you need to change Sheet1!1:1 to whatever line the header is.

And just in case, how this works?

First, we are using the INDEX() formula. This allows us to select a range, then call for which row/column of information we want from that range.

Then, we are using the MATCH() formula. The first instance of it, it checks at the column A and returns in which line the Date field matches. Remember: This will only match the FIRST item. If you have multiple of the same Date in different lines, it won't work.

The second instance of MATCH() we are using to check the entire line of headers to return which column the ACT is. Again, if you have multiple of the same ACT, it will only return the first one.

Now with that, we have a range, and then we have a row number and a column number, returning the exact value you want.

How to match two columns with Excel?

1 Answers1