Get last data formula from column greater is than 1

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,353
Office Version
  1. 2010
Hi everyone,</SPAN></SPAN>

In the below sheet I have formulas and data fill in cells: B2:O20 and cells B21:O21 data comes from another source.</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNO
1Game1234567891011121314
2JR181
3JR191211
4JR20312
5JR2113
6JR2221
7JR2311
8JR24112
9JR25123
10JR2634
11JR27151
12JR28612
13JR293
14JR3014
15JR3111125
16JR3212121236
17JR33
18JR34
19JR35
20JR36
21461412137998761214
Data


I need formula for in cells B22:O22 that can get last data from each column is greater than 1 as shown below</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNO
1Game1234567891011121314
2JR181
3JR191211
4JR20312
5JR2113
6JR2221
7JR2311
8JR24112
9JR25123
10JR2634
11JR27151
12JR28612
13JR293
14JR3014
15JR3111125
16JR3212121236
17JR33
18JR34
19JR35
20JR36
21461412137998761214
2222236
Data Result


Thanks And Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
This returns the last value

B22==IFERROR(IF(LOOKUP(10^100,$B$2:$B$20)<=1,"",LOOKUP(10^100,$B$2:$B$20)),"")
 
Last edited:
Upvote 0
This returns the last value

B22==IFERROR(IF(LOOKUP(10^100,$B$2:$B$20)<=1,"",LOOKUP(10^100,$B$2:$B$20)),"")

Thank you mole999, I entered formula in cell B22 it returns (#¿NOMBRE?) in all the cells </SPAN></SPAN>

I am running excel 2000</SPAN></SPAN>

Regards,
Moti</SPAN></SPAN>
 
Upvote 0
try this

=IF(LOOKUP(10^100,$B$2:$B$20)<=1,"",LOOKUP(10^100,$B$2:$B$20))

I think something is saying to me iferror is a new code
 
Upvote 0
try this

=IF(LOOKUP(10^100,$B$2:$B$20)<=1,"",LOOKUP(10^100,$B$2:$B$20))

I think something is saying to me iferror is a new code

Thank you mole999, Yes this formula do the job only F22, H22, M22 returning </SPAN></SPAN>
( #N/A ) this error can be removed?</SPAN></SPAN>

Regards,
Moti</SPAN></SPAN>
 
Upvote 0
Hi everyone,

In the below sheet I have formulas and data fill in cells: B2:O20 and cells B21:O21 data comes from another source.

ABCDEFGHIJKLMNO
1Game1234567891011121314
2JR181
3JR191211
4JR20312
5JR2113
6JR2221
7JR2311
8JR24112
9JR25123
10JR2634
11JR27151
12JR28612
13JR293
14JR3014
15JR3111125
16JR3212121236
17JR33
18JR34
19JR35
20JR36
21461412137998761214

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Data



I need formula for in cells B22:O22 that can get last data from each column is greater than 1 as shown below

ABCDEFGHIJKLMNO
1Game1234567891011121314
2JR181
3JR191211
4JR20312
5JR2113
6JR2221
7JR2311
8JR24112
9JR25123
10JR2634
11JR27151
12JR28612
13JR293
14JR3014
15JR3111125
16JR3212121236
17JR33
18JR34
19JR35
20JR36
21461412137998761214
2222236

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Data Result

Thanks And Regards,
Moti

B22, copied across:
Rich (BB code):
=IF(COUNTIF(B2:B20,">1"),LOOKUP(9.99999999999999E+307,
  1/(ISNUMBER(B2:B20)*(B2:B20>1)),B2:B20),"")
 
Upvote 0
Try this

=IF(ISERROR(LOOKUP(10^100,$B$2:$B$50)<=1),"",LOOKUP(10^100,$B$2:$B$50))
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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
Back
Top