Estou usando essa 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 tem esses 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 primeiro olhar para Fruit (USUB, 1) e depois Location (USUB, 4) e retornar um valor de Fruit Code (USUB, 2) com base nos critérios da fórmula?
Eu raramente sugeriria usar a função DGET , mas esse é um exemplo perfeito de quando você pode usá-la bem. Veja a imagem abaixo para a configuração e uso
afórmulaé:
=DGET(USUB,"Fruit Code",$A$1:$B$2)
Até tem o benefício adicional de que, se você não colocar um local, apenas encontrará o código para essa fruta.
Para pegar os casos em que não há frutas & combinações de localização que correspondam, veja abaixo:
=IFERROR(DGET(USUB,"Fruit Code",$A$1:$B$2),"Not found")
Você usaria um Index / Match com vários critérios, inserido 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, eu pude usar um código de frutas e localização para retornar um ID. A fórmula resolve corretamente para J . É isso que você estava querendo? Basicamente, você apenas vincula os critérios Match() com & e, em seguida, os intervalos para combiná-los com & e insira como matriz.
Isto irá procurar primeiro para ver se há duplicatas, se não irá retornar o ID correto, independentemente do que é digitado para localização.
Se houver mais de uma fruta, ela procurará o local para encontrar uma correspondência.
Se, em ambos os casos, não houver correspondência a ser encontrada; ou a fruta não existe ou a combinação de fruta e localização, quando há mais de uma fruta, 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"))
