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

#### MarkTheRed

##### New Member
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

### 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
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.

#### sandy666

##### Banned - Rules violations
use XL2BB and post your representative example and expected result

#### MarkTheRed

##### New Member
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
40.1 KB · Views: 3

#### sandy666

##### Banned - Rules violations

with Power Query
 Column1 Column2 Column3 Column4 Column5 Result 1 2 3 4 5 5 1 2 3 4 4 1 2 3 3 1 2 5 5 1 4 4 4 3 4 1 1 1 1 1 1 2 3 2 2

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
with Power Query
 Column1 Column2 Column3 Column4 Column5 Result 1 2 3 4 5 5 1 2 3 4 4 1 2 3 3 1 2 5 5 1 4 4 4 3 4 1 1 1 1 1 1 2 3 2 2

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

Replies
6
Views
89
Replies
4
Views
149
Replies
3
Views
120
Replies
2
Views
115
Replies
25
Views
379

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.

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