Формула 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(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))

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

Итак, я смог использовать Fruit Code и Location, чтобы вернуть ID. Формула правильно разрешается в 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"))