Оператор if с VLookup

У меня возникли проблемы с настройкой функции, которая объединяет два моих листа по нескольким критериям.

Я хочу, чтобы в столбце "Полет" произошло следующее:

IF sheet1.product = sheet2.product AND
sheet1.date >= sheet2.start date AND
sheet1.date <= sheet2.end date THEN
sheet2.flight

Я не могу объединить это и отключить vlookup, потому что я ищу диапазон дат и не могу использовать if (и (потому что мне нужно, чтобы мой value_if_true был динамическим.

Какая формула лучше всего подходит для достижения того, что я пытался объяснить?

Sheet1
For flight column row 1
=IF(AND(D2=Sheet2!A2,Sheet1!A2>=Sheet2!B2,Sheet1!A2<=Sheet2!C2),Sheet2!D2)

Date    Start Date  End Date    product      flight
11/29/2015  11/29/2015  12/5/2015   product1    1
11/29/2015  11/29/2015  12/5/2015   product1    
11/30/2015  11/29/2015  12/5/2015   product1    
11/30/2015  11/29/2015  12/5/2015   product1    
12/1/2015   11/29/2015  12/5/2015   product1    
12/1/2015   11/29/2015  12/5/2015   product1    
12/2/2015   11/29/2015  12/5/2015   product1    
12/3/2015   11/29/2015  12/5/2015   product1    
12/3/2015   11/29/2015  12/5/2015   product1    
12/4/2015   11/29/2015  12/5/2015   product1    
12/5/2015   11/29/2015  12/5/2015   product1    
11/25/2015  11/29/2015  12/5/2015   product2    
11/26/2015  11/29/2015  12/5/2015   product2    
11/27/2015  11/29/2015  12/5/2015   product2    
11/29/2015  11/29/2015  12/5/2015   product2    
11/29/2015  11/29/2015  12/5/2015   product2    
11/30/2015  11/29/2015  12/5/2015   product2    
11/30/2015  11/29/2015  12/5/2015   product2    
12/1/2015   11/29/2015  12/5/2015   product2    
12/1/2015   11/29/2015  12/5/2015   product2    
12/2/2015   11/29/2015  12/5/2015   product2    
12/2/2015   11/29/2015  12/5/2015   product2    
12/3/2015   11/29/2015  12/5/2015   product2    
12/3/2015   11/29/2015  12/5/2015   product2    
12/4/2015   11/29/2015  12/5/2015   product2    
12/4/2015   11/29/2015  12/5/2015   product2    
12/5/2015   11/29/2015  12/5/2015   product2    
12/6/2015   11/29/2015  12/5/2015   product2    

Sheet2
product   start date    end date    flight
product1    11/29/2015  12/1/2015   1
product1    12/2/2015   12/5/2015   2
product2    11/25/2015  11/30/2015  1
product2    12/1/2015   12/2/2015   2
product2    12/3/2015   12/6/2015   3

Любая помощь очень ценится. Благодарю.

Ошибка при изменении ссылки на столбец

Получение # N / A! Значение недоступно ошибка при изменении ссылки на столбец с D на O. Ссылки на ячейки идентичны (раскрывающийся список dataVal с тем же источником), данные ниже для иллюстрации. Как упоминалось в комментариях, эта формула отлично работала до того, как я сделал одно изменение столбца D на O.

формула (используя Ctrl-Shift-Enter):

= ИНДЕКС ('расписание полетов'! $ F $ 3: ИНДЕКС ('расписание полетов'! F: F, MATCH (1E + 99, 'расписание полетов'! F: F)), MATCH (1, IF ((A2> = 'расписание полетов'! $ B $ 3: INDEX ('расписание полетов'! B: B, MATCH (1E + 99, 'расписание полетов'! F: F))) (A2 ‹= 'расписание полетов'! $ C $ 3: INDEX ('расписание полетов'! C: C, MATCH (1E + 99, 'расписание полетов'! F: F))) (O2 = 'расписание полетов'! $ A $ 3: INDEX (' расписание полетов '! A: A, MATCH (1E + 99,' расписание полетов '! F: F))), 1,0), 0))

Это данные на активном листе:

Date    ColB    ColC    Start Date   End Date   ColF    ColG    ColH    Coli        ColJ    ColK    ColL    ColM    flight  Product
11/29/2015  Emp Loc 11/29/2015  12/5/2015   Type    ZZZ dim dim 4850000     2015-11 1   1524    #REF!   ProductA
11/29/2015  Emp Loc 11/29/2015  12/5/2015   Type    ZZZ dim dim 4850000     2015-11 691 2046191 #REF!   ProductA
11/30/2015  Emp Loc 11/29/2015  12/5/2015   Type    ZZZ dim dim 4850000     2015-11 3   1906    #REF!   ProductA
11/30/2015  Emp Loc 11/29/2015  12/5/2015   Type    ZZZ dim dim 4850000     2015-11 1152    2923570 #REF!   ProductA
12/1/2015   Emp Loc 11/29/2015  12/5/2015   Type    ZZZ dim dim 4850000     2015-12 2   3   #REF!   ProductA
12/1/2015   Emp Loc 11/29/2015  12/5/2015   Type    ZZZ dim dim 4850000     2015-12 0   49  #REF!   ProductA
12/2/2015   Emp Loc 11/29/2015  12/5/2015   Type    ZZZ dim dim 4850000     2015-12 0   2   #REF!   ProductA
12/3/2015   Emp Loc 11/29/2015  12/5/2015   Type    ZZZ dim dim 4850000     2015-12 0   433 #REF!   ProductA
12/3/2015   Emp Loc 11/29/2015  12/5/2015   Type    ZZZ dim dim 4850000     2015-12 642 2002431 #REF!   ProductA
12/4/2015   Emp Loc 11/29/2015  12/5/2015   Type    ZZZ dim dim 4850000     2015-12 2   24  #REF!   ProductA

Это данные по «расписанию полетов».

Product  start date  end date       Budget      Rate Flight
ProductA    11/29/2015  11/30/2015  $10,000.00  $5.00   1
ProductA    12/01/2015  12/03/2015  $10,000.00  $5.00   2
ProductA    12/04/2016  12/06/14    $10,000.00  $5.00   3

if-statement excel vlookup
person Nick    schedule 21.04.2016    source источник
comment
Не понимаю, почему _1 _ + _ 2_ не работают.   -  person findwindow    schedule 22.04.2016
comment
Я верю вам, когда вы говорите, что это не работает. Дело в том, почему. Разместите данные, объясняющие, почему.   -  person findwindow    schedule 22.04.2016
comment
Я делаю это для большого стола. формула = IF (AND (D2 = Sheet2! A2, Sheet1! A2 ›= Sheet2! B2, Sheet1! A2‹ = Sheet2! C2), Sheet2! D2) работает, если я смотрю только на одну ячейку, но мне это нужно посмотрите на диапазон, почему я чувствую, что мне нужен vlookup. Я не могу прикрепить образец книги?   -  person Nick    schedule 22.04.2016
comment
Лучше всего размещать данные в виде текста, но при необходимости можно использовать и изображение.   -  person findwindow    schedule 22.04.2016
comment
Вероятно, вам понадобится index/match и, возможно, массив. Если бы здесь был только @scott craner ^ _ ^;   -  person findwindow    schedule 22.04.2016
comment
Итак, что произойдет, если ни один критерий не соответствует, как в первой строке?   -  person findwindow    schedule 22.04.2016
comment
@findwindow, когда вы пытаетесь вызвать меня, вам нужно указать мое имя без пробела, иначе он не отправит мне сообщение. Но см. Ниже.   -  person Scott Craner    schedule 22.04.2016

Ответы (1)


arrow_upward
3
arrow_downward

Тогда вам нужна формула массива:

=INDEX(Sheet2!$D$2:INDEX(Sheet2!D:D,MATCH(1E+99,Sheet2!D:D)),MATCH(1,IF((A2>=Sheet2!$B$2:INDEX(Sheet2!B:B,MATCH(1E+99,Sheet2!D:D)))*(A2<=Sheet2!$C$2:INDEX(Sheet2!C:C,MATCH(1E+99,Sheet2!D:D)))*(D2=Sheet2!$A$2:INDEX(Sheet2!A:A,MATCH(1E+99,Sheet2!D:D))),1,0),0))

Поскольку это формула массива, при редактировании формулы необходимо использовать Ctrl-Shift-Enter вместо Enter или Tab. Если все сделано правильно, Excel поместит {} вокруг формулы.

Вычисления формул массива являются экспоненциальными, поэтому мы хотим ограничить формулу до размеров данных. это то, что делают все INDEX(Sheet2!D:D,MATCH(1E+99,Sheet2!D:D)). Они находят последнюю ячейку в столбце D с номером и автоматически устанавливают ее в качестве нижней ссылки. Таким образом, по мере того, как таблица на листе 2 увеличивается или уменьшается, ссылка на нее увеличивается, используя только необходимые вычисления.

введите описание изображения здесь

person Scott Craner    schedule 21.04.2016
comment
++ Приятно Sheet2!$B$2:INDEX(Sheet2!B:B,MATCH(1E+99,Sheet2!D:D)) - person ; 22.04.2016
comment
Вздох. Я понимаю суть этого, но через 5 минут я все забываю; _; - person findwindow; 22.04.2016
comment
Эта формула прекрасно работает. Я изменил ссылку в окончательном сопоставлении индекса, начинающемся после последнего * 'D2 = Sheet2! $ A $ 2' с D2 на O2, потому что мне нужно сопоставить значения в столбце O вместо D ... достаточно просто, просто измените столбец D на O, но я получаю # N / A. Формула идентична, за исключением того, что я изменил D на O. Значения в Sheet2 находятся в том же месте и имеют одно и то же значение, с той лишь разницей, что столбец подстановки на основном листе изменился с D на O. Есть ли какой-либо базовый QA что вы рекомендуете для формул массива или сопоставления / индекса? Спасибо. @ Скотт Кренер - person Nick; 06.05.2016
comment
@Nick, вы помните Ctrl-Shift-Enter после редактирования формулы? - person Scott Craner; 06.05.2016
comment
Я использую Ctrl-Shift-Enter. проблема должна быть где-то выше по течению, я продолжу анализировать. Спасибо. - person Nick; 06.05.2016
comment
@Nick Убедитесь, что в данных и подстановках нет лишних пробелов или других непечатаемых символов. Это ищет точные совпадения. - person Scott Craner; 06.05.2016