# Lookup Last Value for a Vlookup with non-consecutive data

#### kmaxx98

##### New Member
Good morning,

I am looking for a tweak to a formula I have written in Excel 2010 please.

I am looking for the final balance for a given workorder. For example, for Work Order #5001, I want it to return 10, while for Work Order #6001, I want it to return 1.

I thought I had the nut cracked with the following formula, where R3 is a drop down menu with the Work Order values:

=IF(LOOKUP(R3,\$F\$2:\$F\$7901)=R3,VLOOKUP(R3,\$F\$2:\$J\$7901,5,1),"N/A")

It works like a charm if the data is consecutive, but if it can only handle on set of blank rows inbetween (why 1 set and not 2, I can't figure out)

The formula returns 10 for Work Order #5001, but returns 104 for Work Order #6001.

Any ideas?

Thanks!
 Date Shift Work Order # Item # Physical Cases Planned Physical Cases Produced Balance 6/2/2012 3rd Shift 5001 410164 1101 50 51 6/2/2012 3rd Shift 5001 410164 1101 50 10 6/2/2012 3rd Shift 6/2/2012 3rd Shift 6/2/2012 3rd Shift 6/2/2012 1st Shift 6001 433748 1104 500 604 6/2/2012 1st Shift 6/2/2012 1st Shift 6/2/2012 1st Shift 6/2/2012 1st Shift 6/2/2012 2nd Shift 6001 433748 1104 500 104 6/2/2012 2nd Shift 6/2/2012 2nd Shift 6/2/2012 2nd Shift 6/2/2012 2nd Shift 6/2/2012 EOD Shift 6001 433748 1104 100 1

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2><COL></COLGROUP>

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
can you post better example? I am not sure where 10 or 104 comes from. try to replace
VLOOKUP(R3,\$F\$2:\$J\$7901,5,1)
with
VLOOKUP(R3,\$F\$2:\$J\$7901,5,FALSE)

Hi Storm,

Without being able to post the file, the below is probably the best I can get in here.

The 10 & 104 come from the values in the far right column, under the title "Balance".

When I change it to "false" or "0", I get the first balance value for the Work Order. In this case, for #5001, I get 51, and for #6001, I 604.

Do you see those values in the far right column?

can you post better example? I am not sure where 10 or 104 comes from. try to replace
VLOOKUP(R3,\$F\$2:\$J\$7901,5,1)
with
VLOOKUP(R3,\$F\$2:\$J\$7901,5,FALSE)

You probably want:

=LOOKUP(9.99999999999999E+307,1/(\$F\$2:\$F\$17=\$R3),\$J\$2:\$J\$17)

Good morning,

I am looking for a tweak to a formula I have written in Excel 2010 please.

I am looking for the final balance for a given workorder. For example, for Work Order #5001, I want it to return 10, while for Work Order #6001, I want it to return 1.

I thought I had the nut cracked with the following formula, where R3 is a drop down menu with the Work Order values:

=IF(LOOKUP(R3,\$F\$2:\$F\$7901)=R3,VLOOKUP(R3,\$F\$2:\$J\$7901,5,1),"N/A")

It works like a charm if the data is consecutive, but if it can only handle on set of blank rows inbetween (why 1 set and not 2, I can't figure out)

The formula returns 10 for Work Order #5001, but returns 104 for Work Order #6001.

Any ideas?

Thanks!
 Date Shift Work Order # Item # Physical Cases Planned Physical Cases Produced Balance 6/2/2012 3rd Shift 5001 410164 1101 50 51 6/2/2012 3rd Shift 5001 410164 1101 50 10 6/2/2012 3rd Shift 6/2/2012 3rd Shift 6/2/2012 3rd Shift 6/2/2012 1st Shift 6001 433748 1104 500 604 6/2/2012 1st Shift 6/2/2012 1st Shift 6/2/2012 1st Shift 6/2/2012 1st Shift 6/2/2012 2nd Shift 6001 433748 1104 500 104 6/2/2012 2nd Shift 6/2/2012 2nd Shift 6/2/2012 2nd Shift 6/2/2012 2nd Shift 6/2/2012 EOD Shift 6001 433748 1104 100 1

<tbody>
</tbody>

You guys never fail to amaze me. Help me out with the logic on this if you could...by looking for the largest value possible, you essentially flip the vlookup upside down, right?

What I don't get is why we divide that value by the reference?

You probably want:

=LOOKUP(9.99999999999999E+307,1/(\$F\$2:\$F\$17=\$R3),\$J\$2:\$J\$17)

Got another layer of difficulty if you are up for it Aladin...

I started thinking that it would be really handy to look at this by date, and not just overall. Is there a way to modify, perhaps via a match formula, to look for the date, then look for the last value?

You guys never fail to amaze me. Help me out with the logic on this if you could...by looking for the largest value possible, you essentially flip the vlookup upside down, right?

What I don't get is why we divide that value by the reference?

You guys never fail to amaze me. Help me out with the logic on this if you could...by looking for the largest value possible, you essentially flip the vlookup upside down, right?

What I don't get is why we divide that value by the reference?

http://www.mrexcel.com/forum/showth...k-s-Lookup-Construct-to-Return-the-Last-Match

You might also want to read:

Got another layer of difficulty if you are up for it Aladin...

I started thinking that it would be really handy to look at this by date, and not just overall. Is there a way to modify, perhaps via a match formula, to look for the date, then look for the last value?

Date alone as condition...

=LOOKUP(9.99999999999999E+307,1/(\$D\$2:\$D\$17=\$S3),\$J\$2:\$J\$17)

where S3 is a date of interest.

Date as an additonal condition along with Work order #...

=LOOKUP(9.99999999999999E+307,1/(\$D\$2:\$D\$17=\$S3)*(\$F\$2:\$F\$17=\$R3),\$J\$2:\$J\$17)

Thanks for the help Aladin, but I get the same result with the 2 condition (date & Work Order) as with only 1 condition formula.

For the Work Order 5001:

=LOOKUP(9.99999999999999E+307,1/(\$F\$2:\$F\$7901=\$R3),\$J\$2:\$J\$7901) where R3=Work Order returns a value of 51

=LOOKUP(9.99999999999999E+307,1/(\$A\$2:\$A\$7901=\$R2)*(\$F\$2:\$F\$7901=\$R3),\$J\$2:\$J\$7901) where R3= Work Order and R2=Date (6/1/2012) also returns a value of 51

However, if I understand correctly, but using the 2 condition formula, it should return a value of 101, by isolating the date of 6/1/2012.

Did I miss something?

Again, thanks a ton for the help!

A D F G H I J
 Date Shift Work Order # Item # Physical Cases Planned Physical Cases Produced Balance 6/1/2012 EOD Shift 3002 434831 3102 1700 0 6/1/2012 EOD Shift 3003 423875 3103 1710 1393 6/1/2012 EOD Shift 5001 410164 1101 1000 101 6/1/2012 EOD Shift 6/1/2012 EOD Shift 6/2/2012 3rd Shift 5001 410164 1101 50 51 6/2/2012 3rd Shift 6/2/2012 3rd Shift 6/2/2012 3rd Shift 6/2/2012 3rd Shift 6/2/2012 1st Shift 6001 433748 1104 500 604 6/2/2012 1st Shift 6/2/2012 1st Shift 6/2/2012 1st Shift 6/2/2012 1st Shift

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2><COL></COLGROUP>

Date alone as condition...

=LOOKUP(9.99999999999999E+307,1/(\$D\$2:\$D\$17=\$S3),\$J\$2:\$J\$17)

where S3 is a date of interest.

Date as an additonal condition along with Work order #...

=LOOKUP(9.99999999999999E+307,1/(\$D\$2:\$D\$17=\$S3)*(\$F\$2:\$F\$17=\$R3),\$J\$2:\$J\$17)

Thanks for the help Aladin, but I get the same result with the 2 condition (date & Work Order) as with only 1 condition formula.

For the Work Order 5001:

=LOOKUP(9.99999999999999E+307,1/(\$F\$2:\$F\$7901=\$R3),\$J\$2:\$J\$7901) where R3=Work Order returns a value of 51

=LOOKUP(9.99999999999999E+307,1/(\$A\$2:\$A\$7901=\$R2)*(\$F\$2:\$F\$7901=\$R3),\$J\$2:\$J\$7901) where R3= Work Order and R2=Date (6/1/2012) also returns a value of 51

However, if I understand correctly, but using the 2 condition formula, it should return a value of 101, by isolating the date of 6/1/2012.

Did I miss something?

Again, thanks a ton for the help!
...

You did not miss anything...

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,1/((\$A\$2:\$A\$7901=\$R2)*(\$F\$2:\$F\$7901=\$R3)),\$J\$2:\$J\$7901)

Written a bit differently...

=LOOKUP(9.99999999999999E+307,IF(\$A\$2:\$A\$7901=\$R2,IF(\$F\$2:\$F\$7901=\$R3,1)),\$J\$2:\$J\$7901)

Replies
2
Views
88
Replies
2
Views
162
Replies
3
Views
527
Replies
2
Views
567
Replies
3
Views
865

1,211,788
Messages
6,103,969
Members
447,887
Latest member
ammer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back