Lookup - Last non Zero Value

[biggsy3]

This is the formula that im currently using but I'm hoping someone can help me modify it.

=LEFT(LOOKUP(2,1/(Raw!$A$2:$A$5000&Raw!$B$2:$B$5000=$A8&I$6),Raw!$L$2:$L$5000))

This lookup works great, but im looking for it to find the last non-zero value.

For example my table looks something like this:

Colour Status
Red Pass
Blue Fail
Green
Red
Blue Pass
Green Fail

In this case using that formula to search for Red will result in a 0 as the last Red doesn't have a Status, but what I would like it to do is find the last non-zero value. Can anyone help me?

 

[Aladin Akyurek]

So you have Red in A8, but what do you have in I6?

 

[biggsy3]

Sorry I didnt explain it very well, I tried to use an example that doesn't explain it very well.

A8 is the colour
I6 is another criteria that it looks for in ''Raw!$B$2:$B$5000''

A8 and I6 will always contain something.

However ''Raw!$L$2:$L$5000'' containing the status will not.

Hopefully this makes more sense.

 

[biggsy3]

=LEFT(LOOKUP(2,1/(A2:A10&B2:B10=D1&D2),C2:C10))


Colour____Model____Status_______Red
Red________x_______Pass________x
Blue________y_______Fail
Green_______z_______Pass
Green_______x_______Fail
Red_________x_______(Blank)
Green_______x_______Fail

 

[biggsy3]

Hopefully this example makes a little more sense.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IFERROR(LEFT(LOOKUP(REPT("z",255),IF(Raw!$A$2:$A$5000=$A8,IF(Raw!$B$2:$B$5000=I$6,IF(1-(Raw!$L$2:$L$5000=""),Raw!$L$2:$L$5000))))),"")

 

[biggsy3]

The formula im currently using will return an error for ''Red'' because it's blank, but what I would like it to do is find the last non-zero value.

 

[Aladin Akyurek]

The formula im currently using will return an error for ''Red'' because it's blank, but what I would like it to do is find the last non-zero value.

See post #6 ...

 

[biggsy3]

Wow! Great work! thankyou Aladin worked perfectly. Many thanks

 

[Aladin Akyurek]

Wow! Great work! thankyou Aladin worked perfectly. Many thanks

You are welcome.