cell contents, is also found in a range of cells, then is text in adjacent columns equal

I think that I need an if statement comparing results in one cell with content matched in a vlookup. Let me explain

I have text data in a cell (actually in a long list of cells) which I need to find in a similar column from a report a week earlier... If the data matches, I want to compare the information in the corresponding columns three columns to the left of the selected column and see if they changed. Saying it another way, if the result from a vlookup shows a match, I want to match other information in nearby columns..

I have attached a brief example. The information in the second worksheet would be the same but from a different date.

Thanks in advance

There are instructions at the top of the page explaining how to attach your sample workbook.

The attachment was not included.

You cannot use VLOOKUP if the data is to the LEFT of the matched entry. You would need to use INDEX/MATCH or possibly XLOOKUP if your version of Excel includes that function.

I really don't understand which columns to compare.
What text do you mean? Please explain what you have and what you want to achieve, without suggesting any function

Can you please attach a 2-sheets workbook according to the problem you presented? It would be much easier for me to work if you did this

No idea what you are trying to do with that. Which cell/s has/have the data to match, and which cells have the data you need to match against. And, if you get a match, which cells are you comparing? What happens if you don't get a match?

The original sample workbook is gone, fair enough, but there is no updated workbook.

Put this in column F2 on Sheet 2 and drag across 3 columns, F to H:

Formula:

Please Login or Register  to view this content.

Or, you could use: =IFERROR(INDEX(Sheet1!$A:$C,MATCH($E2,Sheet1!$E:$E,0),MATCH(A$1,Sheet1!$A$1:$E$1,0)),"")

Then, in I2, to compare the cells:

Formula:

Please Login or Register  to view this content.

Drag all the formulae down to the end of the data.

Some problems though. The dates in column A on Sheet 1 are NOT dates. The data in the status columns is not consistent. Bucket Name is OK.

On this basis, it's probably not a good idea to do a combined comparison as it will not highlight any specific anomalies.

So, maybe just

Formula:

Please Login or Register  to view this content.

dragged across and down.

I really appreciate your help... I tried your solution on the 'real file' and it seemed to give me incomplete results (worked on certain rows, not on others). I have recreated my actual file with no data (except in the columns that I am trying to verify the differences) and only column headings. If it isn't too much to ask, can you give me the formulas that I could use with this worksheet. I think I may have erred in trying to transition your formulas from the simplified example I attached yesterday... The new file is called Book7..

Always best to provide a sample workbook that replicates the structure and layout of the original.


Sheet 1, cell AC2:

Formula:

Please Login or Register  to view this content.

Sheet 'USA and CAN 210719' cell AC2:

Formula:

Please Login or Register  to view this content.

Copied back, across, and down as required.