Lookup With Conditions

[Graemea]

Hi,


I need to extract data from a range of data based on 3 criteria. I've used SUMPRODUCT rather than an array formula with INDEX and MATCH:


=SUMPRODUCT((Column A = Product Code)*(Row 2 = Date)*(Row 3 = "Adjusted Sales")*(values to extract))


The problem is that there is a second column with the heading name "Adjusted Sales", containing different data, associated with each
Product Code and Date pairing.


It isn't practical to change the column headings to distinguish the two sets of data at each date associated with the heading "Adjusted Sales".


One way to distingush the second column called "Adjusted Sales" from the other is that it always occurs immediately to the right of a column
with the heading "Sales Rep".


Can someone please suggest how to amend the above SUMPRODUCT formula to distinguish between the two columns headed "Adjusted Sales"?

Or if an INDEX-MATCH construction is easier to do, that would be great too


Thanks very much!

 

[C with no eyes]

I know you said it's not practical to change the heading but how about adding a ` or . so the heading does not match.

If this does not solve your issue could you please provide an example of your current layout, and an example of the layout you want it to return?

 

[Rick Rothstein]

If you headers are in Row 1, start your SUMPRODUCT ranges from Row 2 on down. We could have modified your formula to show you this if you had actually posted your real formula instead of the generic one, non-working one.

 

[Aladin Akyurek]

Care to post a sample using one of the possible methods listed in https://www.mrexcel.com/forum/about-...ml#post2507729 along with a hand-calculated desired result?