Wanting to return values from another sheet =VLOOKUP COLUMN ??

[joshc1994]

I wanting to lookup values in Column M that are below 0 and not OFF HIRE and return the whole Row in relation to that Column.
So search M2:M78 and say return A2:AB2 if the M column is 0 or below and doesn’t say OFF HIRE


My M column looks at 2 dates and checks to see if it is overdue , but if if has a date that it comes off hire this value changes from days overdue to OFF HIRE the formula in M Column is =IF(ISBLANK(U2),L2-TODAY(),"OFF HIRE")


My formula I have tried using to achieve this is =VLOOKUP(A3,Equipment!$M$2:$M$78,COLUMN(Equipment!A2),FALSE)

And my A3 is <0,”OFF HIRE”

Kind Regards
Josh

[joshc1994]

please find photo in relation to M Column and the Row i want to returnAttachment 659128

[hrlngrv]

. . . that are below 0 and not OFF HIRE . . . is 0 or below and doesn’t say OFF HIRE . . . is <0,”OFF HIRE” . . .

Below 0 and 0 or below are NOT the same thing. Add <0, and majority rule would indicate you want negative values.

L2 would be the hire date, U2 would be the off-hire date? M2 would then be OFF HIRE if U2 contains anything, or negative days after hire? M2 could thus be 0 (L2 = TODAY() and U2 blank), negative (L2 < TODAY() and U2 blank), or OFF HIRE (U2 not blank)? You want to find the first value in Equipment!$M$2:$M$78 which isn't 0 and return the entire row from Equipment!$A$2:$AB$78?

If so, simplest to use 29 cells.

Negative Values
AZ2: =MATCH(TRUE,INDEX(Equipment!$M$2:$M$78<0,0),0)

Anything other than OFF HIRE
AZ2: =MATCH(TRUE,INDEX(Equipment!$M$2:$M$78<>"OFF HIRE",0),0)

Results
BA2: =INDEX(Equipment!A$2:A$78,$AZ2)

Fill BA2 right into BB2:CB2.

[joshc1994]

I made a example of my document this might help, i cannot decipher what you want me to do

[hrlngrv]

I added formulas to your example here, and attached below.

[joshc1994]

kool ill give that a try thankyou

[joshc1994]

I got partway with it, there is still an issue i cant rectify. if you have a look at the |Equipment Page| and the |overdue column| and compare that to the |Overdue Page| they don't really match as if they are out by 1

[hrlngrv]

. . . an issue i cant rectify. . . . don't really match as if they are out by 1

You're correct. An off-by-1 error on my part. Change the formula in Overdue!B10 to

Please Login or Register  to view this content.

Then fill that formula down. I fixed the formula in my example workbook on OneDrive. The link in my previous response still works.

[joshc1994]

You're correct. An off-by-1 error on my part. Change the formula in Overdue!B10 to

Please Login or Register  to view this content.

Then fill that formula down. I fixed the formula in my example workbook on OneDrive. The link in my previous response still works.

Legend, all worked, managed to now setup a bunch of these to work for multiple pages.
Many thanks, I was nowhere near getting that to work.