Look Up / Equal To

Hi Group,

I created a chart & have "NA()" in function so the chart's line don't drop to 0s.


In 1 column, I have a series of numbers. Is it possible to do a Look Up / Equal to type of function, such as:

Cell K29 has $34,260 to the last cell K32, which has value of $170,830.

I would like a Look Up Last If type of condition in which if it finds the last value in Column K, then Column L cell would equal the same value.

I attempted to use this, =IF(LOOKUP(E10+99+307,$K$29:$K$35)=K30,"") but it's not working for me.

I'm not sure if it is becaue of the "#N/A" results that gets when using the "NA()" within function.

Thanks

Hi,

Please upload sample spreadsheet with desired result.

I think it is the same spreadsheet as here: http://www.excelforum.com/excel-char...in-charts.html

Hi shukla.ankur281190 & Others,

I attached an example

As you will see, I have values in column K or the purple column.

I'm looking for a function for column L starting with Cell L29.

Intent is to find last value K & if not equal to adjacent cell or Cell K29, then have blank or "#N/A" result so it doesn't chart.

If last value does equal itself, then represent that in adjacent cell or in this example, Cell L32 in which I have the $170, 830


How to do this?

Thanks

Try,

F29=IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)=K29,K29,IF(K29<=$H$19,$H$19-K29)) and drag down.

Formula:

Please Login or Register  to view this content.

Hi shukla.ankur281190,

Thanks for quickly providing feedback.

For my column L if Cell L29 does not equal the last value found in column K, then I'm trying to get the result as a "" (blank) or "#NA" value (for charting.

If Cell L29 does equal the last value found in column K, then then show last value in.

If there are no values beyond last value of column K, then also show nothing.

How is this doable?

I am not getting understand what is you actual conditions.

As you said For my column L if Cell L29 does not equal the last value found in column K, then I'm trying to get the result as a "" (blank) or "#NA" value (for charting. then you said If Cell L29 does equal the last value found in column K, then then show last value in.

Please put the expected result in those cells.

Hi,

I partially got it with your assistance!!

Cell L29
=IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)<>K33,0,IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)=J33,K33

In this example - Anything less than the last value or $170,830 results with a $0.00 - Which is exactly what I want!

If equal, them giving the $170,830

Now, I'm looking the difference between my Not to Exceed (NTE) or budget limit - $217,216 & add cell L32 + Cell J33 ($25,022.40) which represents my Forecast To Completion estimate.

See example

On which cell do you want
Now, I'm looking the difference between my Not to Exceed (NTE) or budget limit - $217,216 & add cell L32 + Cell J33 ($25,022.40) which represents my Forecast To Completion estimate. this condition?

Hi shukla.ankur281190,

Down column L, after last value is found from column K.

In this example - In Cell L32 = K32, then I would Cell L32 to add "Pending or Next Month's Est'd FTC" from column J or $170,830+$25022


Thanks

Try

L29=IF(IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)=K29,K29,IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)<>K29,0))=K29,K29+$J$33,0) drag down

Formula:

Please Login or Register  to view this content.

Hi shukla.ankur281190,

I think we're getting a little closer.

Currently L29, I have:
=IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)=K29,K29,IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)<>K29,0))

This results with, $170,830 & 0s above it - Which is what I'm seeking

Now, with your new function, I'm getting the addition that I'm looking for or $170,830 + $25,022 resulting with $195,852

However, it's not carrying down or summing the next set of rolled down values.

I think we need to combine these functions somehow & I think it will be the final end result or calculations that I'm seeking.

How can we do this?

Thanks

Try

L29=IF(IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)=K29,K29,IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)<>K29,0))=0,0,IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)=K29,K29,IF(LOOKUP(115^115,$K$29:$K$35,$K$29:$K$35)<>K29,0))+$J30) and rag down.

Hi shukla.ankur281190 & Others,

I made another version of my example file.

This time, I created another small table with my Expected Results.

To reiterate what I'm trying to do
1) I receive invoices & document details on left side of table

2) Approve them as long as they're under the Not To Exceed (NTE) limit - Which in this example, is $217, 216 - Found in Cell H19

3) Each Approved Invoice gets "rolled up" in column K & represented with solid reddish-brown line

4) Based on last result of column K, looking to get the "foretasted" roll up if I'm to approve values found in column J - Pending
or Next Month's Est'd FTC (Forecast to Completion) -

-- Column L - ***. Rolling Pending & FTC to NTE - Is where I'm having issues
-- Once I can get this column working correctly, it should be represented like the orange dash line that I drew in - Instead of the green dash line that's not working at the moment

Based off this example - My last result in Column L is $263,266 with a difference of $92,436


Once I can get this working - Then I can use this template to the dozen other projects & invoices that I need to track

Thanks again for your assistance.

Hi shukla.ankur281190 & Others,

Does my last post & file example provide a clearer view of my objectives? If so, please assist.

Thanks

Rather than a lookup, I think I would follow a logic like this. I will try to show the pieces of the formula, but I can't guarantee that I will get the parentheses in place or get the logic perfect.

1) You need to know the maximum in column K. To avoid duplicated effort, and to make the formula in L easier to read and assemble, I prefer to store this kind of calculation in a helper cell -- Let's say K41 for our purposes here, but you can put it anywhere you want. Using Shukla's lookup function K41 =lookup(1e12,$K$29:$K$35)
2) It look like there are two parts to column L
2a) if K is maxK or N/A, then K is maxK+rolling sum of J
2b) if K is less then maxK, then 0 (or the rolling sum of J since the rolling sum of J appears should also be 0)

I would put these parts together like this:

=sum($J$29:j29)+ <-- rolling sum of J part.
if(isna(K29),$K$42, <-- If K29 is na part. If you are using Excel 2013 or newer, you can use the IFNA() function, instead.
if(K29<$K$42,0,$K$42)) <-- If K29 has reached maxK part

Hi MrShorty,

I'm kind of getting your logic but not sure how to put back into my file example. Can you add it in & let's see it's working to my intent.

Thanks

I don't have time right now to put it together. Is there a specific part that you are having trouble with? The lookup() function to find maxK should paste right into your spreadsheet/helper cell as is. The proposed formula for column K should also paste in (if I got the parentheses counted correctly) by removing the "carriage returns" and extra text so that it is all one line.

Try

K29=IF(H29="",0,IF(H29="Pending",0,IF(H29="Forecast",0,SUMIF($H$29:H29,"<>Pending",$I$29:I29))))

L29=IFERROR(IF(LOOKUP(E9+99+307,1/($K$29:$K$35>0),$K$29:$K$35)=K29,K29,L28+J29),0)

And both need drag down.

Hope it will help you

Hi shukla.ankur281190,

Thanks for answering again.

Your K29 example works well. I did have some other variation that worked well to.

Now, 9n L29 - It's giving me part of the desired result in that:

It's giving me the last entry from column K or the $170K value. Now, I'm trying to get it where it sums up the $170K & whatever values found in column J.

I think we finally got it!

=IFERROR(IF(LOOKUP(E9+99+307,1/($K$29:$K$35>0),$K$29:$K$35)=K29,K29,L28+J29),0)+J29

And dragged down

Now, I'm getting summed values after the $170K or from column J & its pending or foretasted values!


Let me verify all works well but I think we finally got!


Thank you very much!

Glad to help you. If your problem get solved please mark it as solved and add reputation by clicking * star icon on left corner on your screen which solutions you helped out.

Hi shukla.ankur281190, MrShorty & Others,

Not quite where I need to be yet. Adding the "+29" at the end to above L29 example helped "show" my pending or FTC value.

However, like my example, I need to get the "rolling sum" from the approximate $170K to the pending or FTCs, such as shown in Cells: L46:L52

Thanks

@mycon73 I don't know what your are doing.

However as per your shown result in cells :L46:L52 this formula =IFERROR(IF(LOOKUP(E9+99+307,1/($K$29:$K$35>0),$K$29:$K$35)=K29,K29,L28+J29),0) will retrieve the desired result in L29:L35.

Please check the attached file and now finally let me know is it what you looking for??

shukla.ankur281190


You Did It! You got the sum to roll up. However & perhaps, this point, it's not doable but I would expect it could be.

Column Ls intent is to show the difference between Approved (column Ls last value) to the Not To Exceed (NTE) limit. Any variance or overage to be shown in Column M. How to get this?


Also, I appears to prevent my lines from starting or dropping to 0s, I need the "NA()" in the functions.

Getting closer.......

Thank you again for assistance.

Try,

K29=IF(H29="",NA(),IF(H29="Pending",NA(),IF(H29="Forecast",NA(),SUMIF($H$29:H29,"<>Pending",$I$29:I29)))) drag down
L29=IFERROR(IFERROR(IF(LOOKUP(E9+99+307,1/($K$29:$K$35>0),$K$29:$K$35)=K29,K29,L28+J29),J29+L28),0) drag down.

Please check the attached file.

"Any variance or overage to be shown in Column M. How to get this?"-> M column having already formula. what would be expected result can you please explain it.

Hi shukla.ankur281190 & Others


Did something change here? Almost looks like the file previously sent.

My column M works fine

As for column L - Intent is only to show the difference between last entry of column K up to the NTE limit or in this example - Up to $217,216 found in cell H19

When the result reaches its NTE, then maybe we need a statement, such as "NTE Met" or just show cap limit downward.

Also & even though I don't like the look, for graphing purposes, looks like we need the "NA()" in forumula so my lines don't start or drop to 0s.

Does this help a little bit more?

Thanks

Hi shukla.ankur281190 & Others

Perhaps, something like this added:

Cell 33 = =IFERROR(IFERROR(IF(LOOKUP(E13+99+307,1/($K$29:$K$35>NA()),$K$29:$K$35)=K33,K33,L32+J33),J33+L32),NA())

How can add the following or some variation of it to above function?

=IF(L33<=$H$19,L32+J33,IF(L33>$H$19,$H$19))

Basically with this - If Cumulative sum from column L is less than NTE value - Then sum keeps rolling up until NTE is reached.

If NTE is reached, then simply show NTE value

Thanks

Hi shukla.ankur281190 & Others

Is combining the following some way doable or is there a better function that works that gets needed intent?

Thanks

= =IFERROR(IFERROR(IF(LOOKUP(E13+99+307,1/($K$29:$K$35>NA()),$K$29:$K$35)=K33,K33,L32+J33),J33+L32),NA())

How can add the following or some variation of it to above function?

=IF(L33<=$H$19,L32+J33,IF(L33>$H$19,$H$19))

Perhaps.

=IFERROR(IFERROR(IFERROR(IF(LOOKUP(E13+99+307,1/($K$29:$K$35>NA()),$K$29:$K$35)=K33,K33,L32+J33),J33+L32),IF(L33<=$H$19,L32+J33,IF(L33>$H$19,$H$19))),NA())

Hi shukla.ankur281190 & Others,

The above works well but the "cumulative rolled up sum" exceeds the Not To Exceed (NTE) budget limit. Now, how to get this to roll up only to the NTE limit & show the difference?

Also,

Once NTE has been reached, I would like to see "#N/A" result from "NA()" somewhere in the function.

Thanks