Grabbing one value for a cell

[kbiro]

Hello,

I think I am close with figuring this out, but I'm stumped. I have a large file about 10,000 lines and I am trying to grab particular information between two sheets.

I have a serial record where I am trying to grab the last date serviced. There is already a column that tells me the last date service on one sheet, but I need to show that on another sheet. The problem I run into is that there may be multiple lines with the same serial number, so it shows the same last serviced date.

I tried a Sumproduct formula, but I get 1/1/1900 as a result. I tried a Sumproduct formula mixed with a MAX formula and gives me the wrong info.Dateexample.xlsm

An example of the file is attached.

Thank you in advance.

[XOR LX]

Hi.

=LOOKUP(1,0/(Sheet1!A$2:A$7=A3),Sheet1!B$2:B$7)

Note that using entire column references within this formula - or indeed any array-processing formula such as SUMPRODUCT, AGGREGATE, and any construction requiring CSE - is not at all a good idea: if you actually only have data up to, say, row 1000, then by referencing entire columns you are forcing Excel to calculate 1047576 more cells than are necessary.

Try choosing a suitable upper bound for the end range reference.

Regards

[kbiro]

Thank you!

[XOR LX]

You're welcome!