Возвращаемое значение в формуле Excel, основанное на просмотре двух отдельных значений (или / или)

Question

Я использую эту формулу, чтобы вернуть значение из таблицы ниже:

=IF(ISNA(VLOOKUP(A2,USUB,1,FALSE)),"No Fruit Found",VLOOKUP(A2,USUB,2,FALSE))

Таблица USUB имеет эти данные:

Fruit          Fruit Code   FruitID Location          CITY
Apple             APP          A    SEATTLE,WA          SEATTLE
Bananas           BAN          B    MODESTO,CA          MODESTO
Cherry            CHER         C    CHARLESTON,SC       CHARLESTON
Blackberrires     BLCKB        D    VICKSBURG,VA        VICKSBURG
Blueberries       BLUB         E    SAN DIEGO,CA        SAN DIEGO
Cantaloupe        CANT         F    GULF SHORES, AL     GULF SHORES
Grapes            GRP          G    NAPA VALLEY,CA      NAPA VALLEY
Peach             PCH          H    ATLANTA, GA         ATLANTA
Grapefruit        GRPFRT       I    FT LAUDERDALE, FL   FT LAUDERDALE
Pomegranate       POM          J    HONOLULU, HI        HONOLULU
Kiwi              KIW          K    SALEM, OR           SALEM

---

Какую формулу я бы использовал, чтобы сначала взглянуть на Fruit (USUB, 1), а затем Location (USUB, 4) и вернуть значение Fruit Code (USUB, 2) на основе критериев в формуле?

Answer 1

Я бы редко предлагал использовать функцию DGET, но это прекрасный пример того, когда вы можете использовать ее с пользой. Смотрите изображение ниже для настройки и использования

формула:

=DGET(USUB,"Fruit Code",$A$1:$B$2)

Он даже имеет дополнительное преимущество: если вы не укажете местоположение, он просто найдет код для этого фрукта.

Чтобы узнать о случаях, когда не найдено ни одной комбинации фруктов и местоположения, см. Ниже:

=IFERROR(DGET(USUB,"Fruit Code",$A$1:$B$2),"Not found")

Answer 2

Вы бы использовали индекс / совпадение с несколькими критериями, введенными в виде массива (с помощью CTRL+SHIFT+ENTER:

=INDEX($C$2:$C$13,MATCH(H3&I3,$B$2:$B$13&$E$2:$E$13,0))

(Отрегулируйте при необходимости)

Итак, я смог использовать код фрукта и местоположение, чтобы вернуть удостоверение личности. Формула правильно разрешается в J Это то, что вы хотели? Вы просто связываете критерии Match() с & , а затем диапазоны, чтобы сопоставить их с & , и вводите как массив.

Answer 3

Сначала будет проверено наличие дубликатов, в противном случае будет возвращен правильный идентификатор независимо от того, что введено для местоположения.

Если есть более одного из этих фруктов, он будет искать место, чтобы найти совпадение.

Если в любом случае совпадение не найдено; либо фрукт не существует, либо комбинация фруктов и местоположения, когда существует более одного фрукта, не является правильной.

=IF(COUNTIF(A:A,H2)>1,IFERROR(INDEX($C$2:$C$13,MATCH(1,INDEX(($D$2:$D$13=I2)*($A$2:$A$13=H2),),0)),"Multiple fruits but Location is wrong"),IFERROR(INDEX(C:C,MATCH(H2,A:A,0)),"Not Found"))

Для ссылки на таблицу используйте следующее:

=IF(COUNTIF(USUB[Fruit],H2)>1,IFERROR(INDEX(USUB[FruitID],MATCH(1,INDEX((USUB[Location]=I2)*(USUB[Fruit]=H2),),0)),"Multiple fruits but Location is wrong"),IFERROR(INDEX(USUB[FruitID],MATCH(H2,USUB[Fruit],0)),"Not Found"))