Valor de retorno da fórmula do Excel com base na observação de dois valores separados (ou/ou)

Question

Estou usando esta fórmula para retornar um valor da tabela abaixo:

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

A tabela USUB possui estes dados:

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

---

Que fórmula eu usaria para examinar primeiro a fruta (USUB,1) e depois a localização (USUB,4) e retornar um valor do código da fruta (USUB,2) com base nos critérios da fórmula?

Answer 1

Eu raramente sugeriria usar oDGETfunção, mas este é um exemplo perfeito de quando você pode fazer bom uso dela. Veja a imagem abaixo para configuração e uso

a fórmula é:

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

Tem ainda a vantagem adicional de que, se você não colocar um local, ele apenas encontrará o código daquela fruta.

Para ver os casos em que não há combinações de frutas e locais correspondentes, veja abaixo:

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

Answer 2

Você usaria um índice/correspondência com vários critérios, inseridos como uma matriz (com CTRL+SHIFT+ENTER:

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

(Ajuste conforme necessário)

Então, consegui usar um Fruit Code e Location para retornar um ID. A fórmula resolve corretamente para J. Era isso que você estava querendo? Basicamente, você apenas vincula Match()os critérios com &e, em seguida, os intervalos para combiná-los com &e insere como array.

Answer 3

Isso verificará primeiro se há duplicatas; caso contrário, retornará o ID correto, independentemente do que for inserido para localização.

Se houver mais de uma fruta, ele procurará o local para encontrar uma correspondência.

Se em ambos os casos não houver correspondência; ou o fruto não existe ou a combinação de fruto e localização, quando há mais de um fruto, não é correta.

=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"))

Para a referência a uma tabela, use isto:

=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"))