Solved Lookup a column, if matches, give me the last number...

February 9, 2015 at 17:38:29
Specs: Windows 7
Hi there,

We are having a problem in our billing where we hire fleet to other departments, and the data entry person is sometimes not matching up the start and end hours.

The issue is that the asset number may not necessarily be in order (e.g. may be multiple entries before i need to match up the start/end hours).

I would love to attach a picture, but im not sure how but i will try to explain..

A2 = Docket number (1234) Manual Entry
B2 = Asset Number (78360) Manual Entry
I2 = Start hours (1311.3) Manual Entry
J2 = End hours (1324.4) Manual Entry

in column B there could be 20 entries of asset numbers, some could be the same as B2, but then not all of them will be.

I want a formula which when i'm entering in A/B as new charge/docket, i want it to look up and see if there is the same asset number, if there is, i want it to look in column J to give me the end hours so that i can match what i am entering in I.

Ideally this would be in another column (say X), so that i can do conditional formatting so to say that if X doesnt match I, then highlight in red, as obviously it doesnt match up and we have some hours that we are missing, or a docket that hasnt been charged...

I hope this makes sense, let me know if you need more information.

See More: Lookup a column, if matches, give me the last number...

Report •

February 10, 2015 at 08:29:35
✔ Best Answer
You might want to investigate "Data Validation" lists as a possible solution,
see here for a very good tutorial:


Report •

February 10, 2015 at 11:25:26
Thanks Mike, but after reading the tutorial, i'm still lost.

Report •

February 10, 2015 at 12:58:27
i did this formula as an array {=MAX(IF($B$2:$B$1000=B2,$K$2:$K$1000))}

however, when i do formula to show up if the start hours do not match the maximum end hours (=IF((AND(B3-B2=0,J3<>C3)),"ERROR")), then it shows an where there shouldnt be, because C2 shows the maximum possible, however i only want it to show the maximum up to that point..


B20 Asset #
C20 Formula {=MAX(IF($B$2:$B$1000=B2,$K$2:$K$1000))}
J20 Start Hours (Manual Entry)
K20 End hours (Manual Entry)
L20 Formula (=IF((AND(B3-B2=0,J3<>C3)),"ERROR"))

I want the formulas to only look up to what has previously been entered, not look at the row im entering (row 20 in this instance..)

Report •

Related Solutions

February 10, 2015 at 13:35:23
i solved it, using.. {=MAX(IF($B$2:$B$1000=B16,$K$2:$K16))} i think....

no it didnt work.

message edited by lkeatley

Report •

February 10, 2015 at 16:21:44
I can not see what your spreadsheet looks like from where I'm sitting,
nor do I have any idea what it is your trying to accomplish.
I have no idea what your doing or why your doing it.

You will have to explain exactly what it is you want,
perhaps post a small sample of your spread sheet,
with a before & after example using Column Letters
and Row Numbers.
But first read this How-To which explains the use of the < PRE > tags to align your data.


Report •

Ask Question