Excel. Search for data in a variable range

There is a table obtained from the program, a huge array, where the date and hour are specified (several rows are issued in one hour), the value (number) and data (the number that needs to be moved to another table) And there is a table in which you need to pull the data: a date column, an hour column, and then many columns with headers the same as the "value" from the first table. For cell K2: It is necessary that the table from the program is viewed, if the date and hour coincide with that in the second one in the table, then look for the value (in column B) from the header (in the example, this is 43) and if there is such a value for this date and hour, then take the data from column C. There are options for how to do this? in the example, you should end up with -156. The simplest VLOOKUP, but how do I explain to vlookup that you only need to view the range for a specific date and hour? https://yadi.sk/i/8_-xzdX0h6Pzgg

 0
Author: skawn, 2019-08-29

1 answers

The range does not need to be calculated.

To facilitate calculations, add a formula (for example, in the column D):

=A2&B2

Since the date is not specified in every line, you need to calculate it:

 ПРОСМОТР(2;1/$I$2:$I2;$I$2:$I2)

And then just: combine the date, hour, and value:

дата&$J2&K$1

And look for the result in column D:

=ИНДЕКС($C$2:$C$100;ПОИСКПОЗ(ПРОСМОТР(2;1/$I$2:$I2;$I$2:$I2)&$J2&K$1;$D$2:$D$100;))

If the additional formula is placed in the first column instead of D (add a column), the formula is somewhat simplified - instead of ИНДЕКС/ПОИСКПОЗ, the data can be pulled using ВПР

 1
Author: vikttur, 2019-08-29 11:43:25