-1

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 |
+-----+----------+-------+
phuclv
  • 30,396
  • 15
  • 136
  • 260
J.Cooker
  • 3

1 Answers1

0

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.

Giacomo1968
  • 58,727
Moacir
  • 286