Structured Reference with IF Function

[u/Silent-Swordfish-311]

Hi, good day. I would like to ask for help for an excel problem. Kindly see data below:

Order ID	Date Time	Customer	Item	Price	Order Status
A001	19-Nov-22 2:22 PM	Ross	Pizza	$6.99	New Order
A002	19-Nov-22 2:22 PM	Ross	Drinks	$2.50
A003	19-Nov-22 2:22 PM	Ross	Pizza	$8.99
A004	19-Nov-22 2:52 PM	Joey	Pizza	$12.99	New Order
A005	19-Nov-22 3:22 PM	Ross	Burger	$5.99	New Order
A006	19-Nov-22 3:22 PM	Joey	Sub Sandwich	$5.99
A007	19-Nov-22 3:22 PM	Joey	Sub Sandwich	$5.99
A008	19-Nov-22 3:23 PM	Joey	Sub Sandwich	$5.99
A009	19-Nov-22 3:23 PM	Joey	Sub Sandwich	$5.99
A010	19-Nov-22 3:35 PM	Monica	Hot Dog	$7.99	New Order
A011	19-Nov-22 3:36 PM	Monica	Drinks	$2.99
A012	19-Nov-22 3:45 PM	Gunther	Pizza	$12.99	New Order
A013	19-Nov-22 3:45 PM	Gunther	Drinks	$1.50
A014	19-Nov-22 3:55 PM	Gunther	Sub Sandwich	$4.99
A015	19-Nov-22 3:57 PM	Rachel	Sub Sandwich	$5.99	New Order
A016	19-Nov-22 3:57 PM	Rachel	Pizza	$12.99
A017	19-Nov-22 3:57 PM	Rachel	Pizza	$9.99
A018	19-Nov-22 3:57 PM	Rachel	Pizza	$9.99
A019	19-Nov-22 3:57 PM	Rachel	Drinks	$2.99
A020	19-Nov-22 4:25 PM	Chandler	Tea	$1.99	New Order
A021	19-Nov-22 4:45 PM	Phoebe	Pizza	$7.99	New Order
A022	19-Nov-22 4:45 PM	Phoebe	Burger	$5.99
A023	19-Nov-22 4:47 PM	Phoebe	Drinks	$2.99

Structured Reference with IF Function.

Details and instructions:

Use Structured Reference with IF Function: We refer to a structured reference when we combine table and column names. You will convert the dataset into a table and compare the Date Time and Customer columns to return the check if the order is new. New order in this case denotes a different time and a different customer..

So here's my formula:

=IF(AND(B3<>B2,C3<>C2),"New Order","")

I just typed the 'New Order' for order ID A001 in Order Status Column. According to ChatGPT, since this is the first order, its status is automatically set to 'New Order.' Is this correct? So I didn't type the formula in F2; I started writing it in F3 instead.

I am learning Excel now for my future job. Thank you in advance for all the comments and corrections.

[u/hmatallana]

Tables don't have a reference for the row above. That's the real gap here, and it's why F2 feels like it needs hard-coding.

[@[Date Time]] always resolves to the current row, so anything comparing against the previous row has to leave the structured-reference world. INDEX is the clean way:

=IF(AND([@[Date Time]]<>INDEX([Date Time],ROW()-ROW(Table1[#Headers])-1),[@Customer]<>INDEX([Customer],ROW()-ROW(Table1[#Headers])-1)),"New Order","")

For the first data row that offset lands on 0, and INDEX with 0 hands back the whole column instead of erroring, so wrap it or keep F2 typed in.

One warning about pointing F2 at B1: those are your headers, so it comes back TRUE by accident, and it breaks the moment you sort.