Index / Match

[Kariba]

Hi, hoping you can help as I can't seem to work this one out. I have three sheets with various data which can occur anywhere in those three sheets. I get updated data every 2 days and need to check if there are any changes.

Data comes in in rows, but data in the three sheets are displayed in columns. eg comes in as 8123, abc, 08:15, but is displayed as
8123
abc
08:15

I need to find the 8123 (for example) which is column F in the data in the three sheets and return the two values below it. I can then conditional format to highlight any differences.

I have the formula below which does work, but only checks one sheet and one column and I need to check all three sheets for a match.

=INDEX('sheet1'!$F$1:$F$51,MATCH("F7,'sheet1'!$F$1:$F$51,0)+1,1)

Thank you

 

[awoohaw]

My suggestion is more old school. But, I suggest just making 2 columns, one for each sheet, so 6 new columns.
I'm sure there is a way to use TOCOL or TOROW and a LET function. But, to do that could you post some sample mini worksheets (1 from each sheet) using the xl2bb add in?

 

[Kariba]

Source data as it comes in at the top, output below. I've changed some of the data.

Only interested in checking data in columns D,F and Q against the output to see if anything has changed. There's thousands of rows coming in every two days so impossible to check manually.

As an afterthought, I'd also need to know if there's anything on the output that no longer on the source data.

02 APRIL SUNDAY.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	15/03/2023	02/04/2023 00:00	ABC	A123	64	1234		02/04/2023 04:35									05:35
2	15/03/2023	02/04/2023 00:00	ABC	A123	54	5678		02/04/2023 04:55									05:55
3	15/03/2023	02/04/2023 00:00	ABC	A456	666	10122		02/04/2023 04:55									05:55
4	15/03/2023	02/04/2023 00:00	ABC	A456	882	14566		02/04/2023 04:55									05:55
5	15/03/2023	02/04/2023 00:00	ABC	A678	217	19010		02/04/2023 05:00									06:00
6	15/03/2023	02/04/2023 00:00	ABC	A456	40	23454		02/04/2023 05:10									06:10
7
8
9	1234	5678	10122
10	A123	A123	A456
11	05:35	05:55	05:55
12	14566	19010	23454
13	A456	A678	A456
14	05:55	06:00	06:10
Sheet1

Cell Formulas
Range	Formula
Q1:Q6	Q1	=TIMEVALUE(TEXT(H1,"HH:mm"))+TIME(1,0,0)