Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a column of data lets say in P:P, I used the formula =LOOKUP(2,1/(NOT(ISBLANK(P:P))),P:P) to return the last value in this data. However now I need another formula to return another value from the same selection that is not the result of the above formula. Do you know if I can do this via formulas?
Solved by C. J. in 21 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 02/05/2018 - 08:00
Hi…Welcome to Got It Pro
User 02/05/2018 - 08:00
Hi there
Excelchat Expert 02/05/2018 - 08:01
Could you please share the data?
User 02/05/2018 - 08:01
okay I can create a quick simulation and send now
Excelchat Expert 02/05/2018 - 08:01
Excelchat Expert 02/05/2018 - 08:02
You can also do a demo in this blank sheet at the right
User 02/05/2018 - 08:03
hmmm I expected this to give me 10
User 02/05/2018 - 08:04
I am not sure why this is not giving me the last result in this range now
User 02/05/2018 - 08:04
it works in my personal data on excel 16
Excelchat Expert 02/05/2018 - 08:05
let me check
Excelchat Expert 02/05/2018 - 08:08
ok...google sheet has a little different logic
Excelchat Expert 02/05/2018 - 08:08
otherwise your formula is correct
Excelchat Expert 02/05/2018 - 08:09
You see now it works
User 02/05/2018 - 08:09
Okay well my issue is, when this formula works, I need another cell the then scan A:A again, and return the last value that is not the result in C!
User 02/05/2018 - 08:10
Ah yes I see now. So I need a formula in C2 that does as I said above
User 02/05/2018 - 08:10
I am not sure how to create that using lookup
Excelchat Expert 02/05/2018 - 08:11
so are you saying you want to return the last but one value with another formula?
User 02/05/2018 - 08:12
I want a formula in cell C", that looks at A:A and returns the last value that is not the value in C1 if this makes sense
User 02/05/2018 - 08:12
Excelchat Expert 02/05/2018 - 08:13
so you want 10 in C2, as per your example
User 02/05/2018 - 08:13
Exactly :)
Excelchat Expert 02/05/2018 - 08:19
please check now
Excelchat Expert 02/05/2018 - 08:19
this is a simpler formula than the lookup
Excelchat Expert 02/05/2018 - 08:20
Does that solve your query?
User 02/05/2018 - 08:20
Yes this looks perfect!
User 02/05/2018 - 08:20
Thank you so much for your help
Excelchat Expert 02/05/2018 - 08:20
Is there anything else I can help you with this query?
User 02/05/2018 - 08:20
No this is all thank you and again thank you for your help
Excelchat Expert 02/05/2018 - 08:21
Please do give me a good feedback for my service.
Excelchat Expert 02/05/2018 - 08:21
Thanks for your time. Please do come back for any new question. Have a great day ahead!
User 02/05/2018 - 08:21
I definitely shall and have a good day also

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc