Help, compare data between two sheets and show change

[MarcBK]

Hello all,

I am really stuck and do not even know where to start, I am trying to compare data from survey data between three sheets based on the names populated in sheet 3.

Survey Result data is in sheet 1 and 2.

Sheet 3 is formula collected data tables / product names
Sheet 2 is initial survey collection data
Sheet 1 is survey change data based on time improvement or decline (the data improvement or decline over a period of time that has been collected)

The question header is in L2 on sheet 2 and results are in column L rows 3:3000 this is the question/results I am trying to compare with the below

The question header is in P2 on sheet 1 this is the question I am trying to compare with the above to see if improvement or decline, and results are Column P rows 3:3000

In sheet three I have pulled the names of the products I am comparing and now want to lookup the questions and results and compare them between sheet 1 and 2.

The idea is to lookup based on product name ( which is found in sheets 1,2 and sheet3) (names are pulled into sheet three using a filter, countif formula based on information coming from sheet 1 and sheet 2)

I want to find the product names in sheet 2 and sheet 1 but compare the data results (column L and P) and show reflecting the change / improvement / decline gives. (The results data for the products is found in cells L3:3000 in sheet2 and P3:3000 in sheet1)

If data criteria is better in sheet 1 compared to sheet 2s data then return the resultant text "improved" but if the answer is worse then return the resultant text "declined" if no change shiw "no change".

The data result criteria to compare results is using a scale of Poor, Fair, Good, Very Good, Excellent.

So result for data P3 sheet 1 might be excellent but then in L3 sheet 2 for the same product it might be poor as an example.

I hope that makes sense, is some able the please help me with this? Many many thanks.

 

[ZMyrrh]

Try this. You will need to change some of this to make it fit your specific spreadsheet. You will need to Make a small table that says [Excellance][5], [Very Good][4] and so on. Then change the Lookup_Range and Return_Array to that sheet and those cells. =LET(Lookup_Range,Sheet1!$T$1:$T$5,Return_Array,Sheet1!$U$1:$U$5,ShtOne,XLOOKUP(O3,Lookup_Range,Return_Array),ShtTwo,XLOOKUP(Sheet2!K3,Lookup_Range,Return_Array),IFS(ShtOne>ShtTwo,"Declined",ShtOne=ShtTwo,"No Change",ShtOne<ShtTwo,"Improved"))

 

[Peter_SSs]

@ZMyrrh
The OPs profile shows Excel 2019 so they will not have the LET function.

 

[ZMyrrh]

Without using the LET function it looks a little more messy. You might need to change these ranges to meet your needs. I hope this helps! =IFS(XLOOKUP(O3,Sheet1!$T$1:$T$5,Sheet1!$U$1:$U$5)>XLOOKUP(Sheet2!K3,Sheet1!$T$1:$T$5,Sheet1!$U$1:$U$5),"Declined",XLOOKUP(O3,Sheet1!$T$1:$T$5,Sheet1!$U$1:$U$5)=XLOOKUP(Sheet2!K3,Sheet1!$T$1:$T$5,Sheet1!$U$1:$U$5),"No Change",XLOOKUP(O3,Sheet1!$T$1:$T$5,Sheet1!$U$1:$U$5)<XLOOKUP(Sheet2!K3,Sheet1!$T$1:$T$5,Sheet1!$U$1:$U$5),"Improved"))