OFFSET INDEX MATCH Formula

[brian sullivan]

I'm reaching out to best maximize my implementation of the OFFSET/INDEX/MATCH Formula.

My goal is to populate columns (K3) & (K4) with the corresponding fields to the left (Dest, City, Size, & value) with one DROPDOWN formula. My initial thoughts are that you would definitely use as baseline (OFFSET, INDEX, MATCH, MATCH).

Thank you everyone who reads this item. I'm sure the solution is not that complex in nature.

This section matches Destination Country
=OFFSET(INDEX(E:E,MATCH(H2,C:C,0)),0,0,,)

This section matches Destination City
This formula captures the E2 (110)
=OFFSET(INDEX(E:E,MATCH(I2,D:D,0)),0,0,,)
This formula captures the cell F2 (8)
=OFFSET(INDEX(E:E,MATCH(I4,D:D,0)),0,1,,)

However, I want to be able to match cols (C & D), & then utilize an offset that captures the value in both (E2) & (F2).

 

[NARAYANK991]

Hi ,

In K3 , try this :

=SUMPRODUCT(($C$2:$C$20=$H3)*($D$2:$D$20=$I3)*($E$1:$F$1=$J3)*($E$2:$F$20))

Copy this down.

Narayan

 

[eibi]

Brian,

Welcome to the forum!

See attached file; using SUMPRODUCT (Narayan beat me to it, above)

...Does this accomplish what you are wanting? It's not in the OFFSET format you initially proposed.

 

[brian sullivan]

Thanks for the feedback here. I will follow-up.