Formula to return last value from list - potential nested If issue

MarkTheRed

New Member
Joined
Jan 5, 2017
Messages
7
Hello everyone,

I have what will be a very complex issue (i think), and simply said i do not know how to solve it. Some have adviced VBA, but for reasons of my own i would like to have it in simple formula. Here is an explanation of what is needed. Let's say i have a list of cells with data in it (A1-A5):
1 2 3 4 5

In A6 i need a formula that would always return the data from the last cell (A5), by looking up data from A5 - A1. The catch is that there are times where the values will not be present. Here are some examples:

1 2 3 4 5 - 5
1 3 4 5 - 5
1 1 1 1 1 - 1
3 3 - 3
2 3 - 3

So by looking at these examples, you see that after - i have placed the number that will be the final result in A6, and these examples are some situations that can occur. I have tried using IF with combinations of AND, OR, and i cannot get a proper solution. I know the nested if has the potential to be huge but that is something i will have to live with i guess.

Any assistance would be much appreciated. If someone needs an example in excel (if these are confusing) please let me know.

Thank you
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

MarkTheRed

New Member
Joined
Jan 5, 2017
Messages
7
Please bear in mind that, as i've noticed the board does not separate them, any cell from A1-A5 that is empty, would need to be in the calculation, in case at some point data is entered into them.
 

MarkTheRed

New Member
Joined
Jan 5, 2017
Messages
7
use XL2BB and post your representative example and expected result
I am unable to install XL2BB, so i have attached a simple image, hope this helps. It should illustrate what i need. The G cells formula should check, starting from E to A, which one contains the "latest" data, and take into consideration empty cells and repeated data.

Thanks
 

Attachments

  • examples.JPG
    examples.JPG
    40.1 KB · Views: 3

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

with Power Query
Column1Column2Column3Column4Column5Result
123455
12344
1233
1255
1444
3411
1111
2322

Code:
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}, "en-GB"),{"Column1", "Column2", "Column3", "Column4", "Column5"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result"),
    LastChar = Table.TransformColumns(Merge, {{"Result", each Text.End(_, 1), type text}})
in
    LastChar
 

MarkTheRed

New Member
Joined
Jan 5, 2017
Messages
7
with Power Query
Column1Column2Column3Column4Column5Result
123455
12344
1233
1255
1444
3411
1111
2322

Code:
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}, "en-GB"),{"Column1", "Column2", "Column3", "Column4", "Column5"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result"),
    LastChar = Table.TransformColumns(Merge, {{"Result", each Text.End(_, 1), type text}})
in
    LastChar
Thank you for this i will test it out. There is a possibility that the cells might be from different sheets, so i will think on how to adapt this, if possible.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
example
A1= sheet30!C34
B1= sheet150!D3
etc...
but the result should be like you see in blue table
then you can use Power Query
PQ is designed to work with data not a single cells

just select range,
ribbon - Data - From Table....
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,674
Members
415,920
Latest member
ExcelNoob28

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