Lookup Last Value for a Vlookup with non-consecutive data

kmaxx98

New Member
Joined
Jun 17, 2012
Messages
14
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</SPAN>Shift</SPAN>Work Order #</SPAN>Item #</SPAN>Physical Cases Planned</SPAN>Physical Cases Produced</SPAN>Balance</SPAN>
6/2/2012</SPAN>3rd Shift</SPAN>5001</SPAN>410164</SPAN>1101</SPAN>50</SPAN>51</SPAN>
6/2/2012</SPAN>3rd Shift</SPAN>5001</SPAN>410164</SPAN>1101</SPAN>50</SPAN>10</SPAN>
6/2/2012</SPAN>3rd Shift</SPAN>
6/2/2012</SPAN>3rd Shift</SPAN>
6/2/2012</SPAN>3rd Shift</SPAN>
6/2/2012</SPAN>1st Shift</SPAN>6001</SPAN>433748</SPAN>1104</SPAN>500</SPAN>604</SPAN>
6/2/2012</SPAN>1st Shift</SPAN>
6/2/2012</SPAN>1st Shift</SPAN>
6/2/2012</SPAN>1st Shift</SPAN>
6/2/2012</SPAN>1st Shift</SPAN>
6/2/2012</SPAN>2nd Shift</SPAN>6001</SPAN>433748</SPAN>1104</SPAN>500</SPAN>104</SPAN>
6/2/2012</SPAN>2nd Shift</SPAN>
6/2/2012</SPAN>2nd Shift</SPAN>
6/2/2012</SPAN>2nd Shift</SPAN>
6/2/2012</SPAN>2nd Shift</SPAN>
6/2/2012</SPAN>EOD Shift</SPAN>6001</SPAN>433748</SPAN>1104</SPAN>100</SPAN>1</SPAN>

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

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

storm8

Active Member
Joined
Apr 7, 2010
Messages
327
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)
 

kmaxx98

New Member
Joined
Jun 17, 2012
Messages
14
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)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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>
 

kmaxx98

New Member
Joined
Jun 17, 2012
Messages
14

ADVERTISEMENT

Genius Aladin,

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)
 

kmaxx98

New Member
Joined
Jun 17, 2012
Messages
14
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?

Genius Aladin,

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Genius Aladin,

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?

Already done so:

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

You might also want to read:

http://www.mrexcel.com/forum/showthread.php?102091-9-9999999
http://www.mrexcel.com/forum/showthread.php?310278 (post #7)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

kmaxx98

New Member
Joined
Jun 17, 2012
Messages
14
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</SPAN>Shift</SPAN>Work Order #</SPAN>Item #</SPAN>Physical Cases Planned</SPAN>Physical Cases Produced</SPAN>Balance</SPAN>
6/1/2012</SPAN>EOD Shift</SPAN>3002</SPAN>434831</SPAN>3102</SPAN>1700</SPAN>0</SPAN>
6/1/2012</SPAN>EOD Shift</SPAN>3003</SPAN>423875</SPAN>3103</SPAN>1710</SPAN>1393</SPAN>
6/1/2012</SPAN>EOD Shift</SPAN>5001</SPAN>410164</SPAN>1101</SPAN>1000</SPAN>101</SPAN>
6/1/2012</SPAN>EOD Shift</SPAN>
6/1/2012</SPAN>EOD Shift</SPAN>
6/2/2012</SPAN>3rd Shift</SPAN>5001</SPAN>410164</SPAN>1101</SPAN>50</SPAN>51</SPAN>
6/2/2012</SPAN>3rd Shift</SPAN>
6/2/2012</SPAN>3rd Shift</SPAN>
6/2/2012</SPAN>3rd Shift</SPAN>
6/2/2012</SPAN>3rd Shift</SPAN>
6/2/2012</SPAN>1st Shift</SPAN>6001</SPAN>433748</SPAN>1104</SPAN>500</SPAN>604</SPAN>
6/2/2012</SPAN>1st Shift</SPAN>
6/2/2012</SPAN>1st Shift</SPAN>
6/2/2012</SPAN>1st Shift</SPAN>
6/2/2012</SPAN>1st Shift</SPAN>

<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)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,487
Messages
5,596,450
Members
414,068
Latest member
FAH

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top