Vlookup and its issue when there are repeated ref value

welcome to the forum, sanl_2003. do upload an excel sample so that we do not have to manually key in your data to do a testing. input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

my guess is if your data is in A1:C4, and WO given is in G2, try:
=LOOKUP(2,1/(($A$2:$A$4=G2)*($C$2:$C$4)),$B$2:$B$4)

the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

sure no worries.

1. well, LOOKUP is more flexible for this scenario where i have to base on WO to get a latest date, and then return a status. VLOOKUP could probably only base on WO to get the latest date. and that is provided that the date is in ascending order.

2 & 3. i have something similar explained in the file of my link below*in my signature. Tips & Tutorials I Compiled, sheet 6. you may take a look at it. a useful trick for you to learn how formulas work is to select a portion of the formula in the formula bar and press the F9 key to calculate. basically, this portion below is a logical test.
($A$2:$A$4=G2)
it either turns out TRUE or FALSE. Excel recognizes TRUE as 1 and FALSE as 0. so based on your case, if you select the first one and press F9, you will get:
{TRUE;TRUE;TRUE}
you will get FALSE if your WO is not 1585.

($C$2:$C$4) are your dates. Press F9 would give you:
{42948;42976;42993}

so if you multiply TRUE (1) and a number, you still get the same number. press F9 for this and you get the same dates
($A$2:$A$4=G2)*($C$2:$C$4)
{42948;42976;42993}

1 divided by any number is a maximum of 1. 1 / 0 is DIV Error. 1/1 is 1. 1/100 is 0.01. so 1/(($A$2:$A$4=G2)*($C$2:$C$4)) are numbers smaller than 1:
{0.0000232839713141473;0.0000232688011913626;0.0000232596004000651}

my lookup_value is "2", so since the LOOKUP cannot find an exact match, it returns the last number found, and then picks the corresponding Material Status.

i just realized my formula will not work if your dates are not sorted, since it will always give you the last date even if it's not the latest. if your dates are sorted, you can search instead for an extremely big number and it will return the last one.
=LOOKUP(99^99,($A$2:$A$4=G2)*($C$2:$C$4),$B$2:$B$4)
or if your data is unsorted, then:
=LOOKUP(2,1/(MAX(($A$2:$A$4=G2)*($C$2:$C$4))=($A$2:$A$4=G2)*($C$2:$C$4)),$B$2:$B$4)

4. you may download my Tips & Tutorial instead. That will be a summary of what i've learnt here.