Come utilizzare più VLOOKUP con dati duplicati?

Ho un enorme file excel. La colonna A ha fatture (righe duplicate poiché each elemento della fattura è una row), la colonna B ha valore SKU dell'object acquistato (come 200ml, 300ml ecc.), La colonna C ha acquistato la marca (come Coca-Cola, Sprite ecc.) la colonna D ha il numero di oggetti acquistati (come 10,15 ecc.).

Tabella 1

La prima tabella è il file dump per tutte le fatture e gli inthemes acquistati

Ora voglio trovare il numero di articoli acquistati a condizione che il marchio sia Coca-Cola, lo SKU è 200ml e la fattura no. è XAX1X2X3 e visualizzarla in un'altra cella.

Tavolo 2

Ora nella seconda tabella, voglio abbinare la fattura con la dimensione del pacco e il marchio dalla prima tabella e mettere la quantità nella cella vuota

Quindi la row evidenziata nella tabella 2 mostrerà il valore 3 per la fattura T1411031400114, dimensione confezione 200, il brand coca-cola ha Qty come 3.

Stavo pensando di utilizzare VLOOKUP annidato, ma non posso get la formula corretta per esso.

Qualsiasi aiuto sarà apprezzato.

Saluti

Anand

Solutions Collecting From Web of "Come utilizzare più VLOOKUP con dati duplicati?"

Per una tabella di esempio come

 +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ B | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ G | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ H | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 1 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ Dimensione | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ Dimensione | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 2 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 31 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 200 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 1 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 31 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 200 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 3 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 3 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 31 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 200 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 2 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 4 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 31 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 300 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 4 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 5 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 31 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 100 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 3 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 31 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 300 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 2 | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | | A | B | C | D |E | F | G | H | I | +---+---------+--------+------+-----+--+---------+-------+------+-------+ | 1 | Invoice | Brand | Size | Qty | | Invoice | Brand | Size | Total | | 2 | 31 | Coke | 200 | 1 | | 31 | Coke | 200 | 3 | | 3 | 31 | Coke | 200 | 2 | | | | | | | 4 | 31 | Sprite | 300 | 4 | | | | | | | 5 | 31 | Fanta | 100 | 3 | | | | | | | 6 | 31 | Coke | 300 | 2 | | | | | | +---+---------+--------+------+-----+--+---------+-------+------+-------+ 

utilizzeresti nella cella I2

 =SUMPRODUCT(($D$2:$D$6)*(F2=$A$2:$A$6)*(G2=$B$2:$B$6)*(H2=$C$2:$C$6)) 

Speriamo che questo risolve (alless parte del) il tuo problema. Questo fornisce una delle tante uscite di una tabella pivot. Pro: il risultato viene aggiornato automaticamente (una tabella pivot non è). Contro: non si può desiderare un aggiornamento automatico (per mantenere i risultati precedenti o per problemi di performance con grandi set di dati).

 =OFFSET($C$9, SMALL(IF(D10=($B$10:$B$38), ROW($B$10:$B$38)-9),COUNTIF($B$10:$B10,D10)),0) 

Utilizza questa formula. Attenzione, però, con riferimento alle cellule.

Consideriamo l'utilizzo di sumifs ():

ad esempio: =SUMIFS($F:$F,$C:$C,$J3,$D:$D,K$1,$E:$E,K$2)

Controlla l'image sottostante per una chiarezza

Utilizzo di sumi per risolvere il problema