index Match not producing proper results

I have a rather large workbook and have been using a SUMIFFS formula that is starting to become a little cumbersome and i need to modify to alleviate constant corrections.

My example I posted has 3 sheets.

sheet "TEST1" has a pivot table of data pulled from my server. The problem is some lines will be removed over time.

Sheet "TEST2" and "TEST3" Has data pulled from TEST1

The problem i am having is I have written the formula to look in a specific row and Cell so when a row is deleted I have to modify to formula to look in the correct cell. The funny part is probably has worked for several years until recently but i do realize there is a better way just not found it yet.

my formula is below:

=SUMIFS(Test1!C8,Test1!A8,$AJ$7,Test1!B8,"G") I tried to keep make sure i referenced the specific item in AJ7.

I think i should transition this formula to probably a Vlookup but open to any suggestions

royUK,

I do not have a good answer for that. At the time I was working this project I was experimenting with sumifs. This formula worked for many years until input data was rightly changed. when it did i had to fix an onerous amount of formulas to get workbook back up and running. It does ask for the sumifs of one cell which which returns the year. In this case 2021 Column. My desire would be to not have modify code if a row is deleted on test1 sheet. Thanks Billyrj

Okay I have been playing with this for a little bit and am struggling with how to resolve.

On Sheet "Test2" I have aray of data and in cell AK8 my formula "=SUMIFS(Test1!C8,Test1!A8,=SUMIFS(Test1!C8,Test1!A8,$AJ$7,Test1!B8,"G"),Test1!B8,"G")" its was pointed out that i was using a sumifs to return 1 cell. I need to look in "$AJ$7" as a reference to find in column A on sheet "TEST1" find "CAT" then if then match G and copy columns C:I to TEST2.

I am essentially matching two columns and parsing data. And cannot figure out a better approach with the data i am using. I have tried to use Vlookup and have really struggled.

Thanks in

I have attempted to utilize a Vlookup but still struggling with returning the correct value.

In K11 I have a formula that is looking in Column "A" for "DOG" and then in Column "B" for "G" then return the value in column "C".

My formula: =VLOOKUP(K3, IF(B2:B19="G", A2:I19, ""), 3,FALSE )

I did reference cell K3 as it has 'DOG" but just doesn't seem to work. I attached and updated book. I originally wanted to copy and paste Column C:E but just could not figure that out. Thanks

I have come full circle on this project. My original Formula was a sumifs but was not the correct direction. I am attempting to use Index Match formula and not getting the desired results.

My desire is to Look in column "A" and find a match to K3

Second look in Column "B" and find Match to K4

Provide the value of Column "C"

with my data in the work sheet my result should be 36 and i am getting a value error.

My current formula is

=INDEX(C2:C10, MATCH(1,(K3=$A$2:$A$10) * (K4=$B$2:$B$10),0))

I would love to find the values in C:i and transpose accordingly but am trying baby steps and trying to find value in "C" for now.

Thanks

MR,

Appreciate the review but honestly do not know how to do what you saying but will research.

Thanks

Pecoflyer,

Thank you as i have been working this for so long it became a quest. It works perfect and cant thank you enough. I unfortunately have some gaps in my working with formulas and could visualize working SUMPRODUCT. More work to on my end and again very thankful for for your effort.