Formula to get the range of certain Category

N

nazarene

New Member
Joined
Jun 25, 2015
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Dear Sir,
Can you please help me to make a formula in excel with the following category?
in the table below , I want to get the height type for each item. example. Type "Independents (IN)" the height is 2.03, so the height type should be 6, because 0 to 6 the height type is 6, etc. , another example Type "Suspended Floors (SF)" the height is 6.01 so the height type is 8. can you do it to all the category"?
thanks in advance. I also put the screen shots, because i cannot -arrange as it is, as I cannot see also the attachment for excel files

ITEM NO. Type Height DESCRIPTION
type
IST-01 Stair Towers (ST) 6 0-6 Installation of Stair Towers (IST) from ground up to 6.00 m
IST-02 Stair Towers (ST) 8 6.0-8 Installation of Stair Towers (IST) from 6.01 m up to 8.00 m
IST-03 Stair Towers (ST) 10 8.01-10 Installation of Stair Towers (IST) from 8.01 m up to 10.00 m
IST-04 Stair Towers (ST) 12 10.01-12 Installation of Stair Towers (IST) from 10.01 m up to 12.00 m
IST-05 Stair Towers (ST) 14 12.01-14 Installation of Stair Towers (IST) from 12.01 m up to 14.00 m
IST-06 Stair Towers (ST) 16 14.01-16 Installation of Stair Towers (IST) from 14.01 m up to 16.00 m
IST-07 Stair Towers (ST) 18 16.01-18 Installation of Stair Towers (IST) from 16.01 m up to 18.00 m
IST-08 Stair Towers (ST) 20 18.01-20 Installation of Stair Towers (IST) from 18.01 m up to 20.00 m
IST-09 Stair Towers (ST) 22 20.01-22 Installation of Stair Towers (IST) from 20.01 m up to 22.00 m
IST-10 Stair Towers (ST) 24 22.01-24 Installation of Stair Towers (IST) from 22.01 m up to 24.00 m
IST-11 Stair Towers (ST) 26 24.01-26 Installation of Stair Towers (IST) from 24.01 m up to 26.00 m
IST-12 Stair Towers (ST) 28 26.01-28 Installation of Stair Towers (IST) from 26.01 m up to 28.00 m
IST-13 Stair Towers (ST) 30 28.01-30 Installation of Stair Towers (IST) from 28.01 m up to 30.00 m
IST-14 Stair Towers (ST) 32 30.01-32 Installation of Stair Towers (IST) from 30.01 m up to 32.00 m
IST-15 Stair Towers (ST) 34 32.01-34 Installation of Stair Towers (IST) from 32.01 m up to 34.00 m
IST-16 Stair Towers (ST) 36 34.01-36 Installation of Stair Towers (IST) from 34.01 m up to 36.00 m
IST-17 Stair Towers (ST) 38 36.01-38 Installation of Stair Towers (IST) from 36.01 m up to 38.00 m
IST-18 Stair Towers (ST) 40 38.01-40 Installation of Stair Towers (IST) from 38.01 m up to 40.00 m
IST-19 Stair Towers (ST) 42 40.01-42 Installation of Stair Towers (IST) from 40.01 m up to 42.00 m
IST-20 Stair Towers (ST) 44 42.01-44 Installation of Stair Towers (IST) from 42.01 m up to 44.00 m
IST-21 Stair Towers (ST) 46 44.01-46 Installation of Stair Towers (IST) from 44.01 m up to 46.00 m
IST-22 Stair Towers (ST) 48 46.01-48 Installation of Stair Towers (IST) from 46.01 m up to 48.00 m
IST-23 Stair Towers (ST) 50 48.01-50 Installation of Stair Towers (IST) from 48.01 m up to 50.00 m
IST-24 Stair Towers (ST) 51 50.01-Above Installation of Stair Towers (IST) above 50 m

IWG-01 Walkways (WG) 8 6.01-8 Installation of Walkways (IWG) , by means of scaffolds built from 6.01 m up to 8.00 m
IWG-02 Walkways (WG) 10 8.01-10 Installation of Walkways (IWG) , by means of scaffolds built from 8.01 m up to 10.00 m
IWG-03 Walkways (WG) 12 10.01-12 Installation of Walkways (IWG) , by means of scaffolds built from 10.01 m up to 12.00 m
IWG-04 Walkways (WG) 14 12.01-14 Installation of Walkways (IWG) , by means of scaffolds built from 12.01 m up to 14.00 m
IWG-05 Walkways (WG) 16 14.01-16 Installation of Walkways (IWG) , by means of scaffolds built from 14.01 m up to 16.00 m
IWG-06 Walkways (WG) 18 16.01-18 Installation of Walkways (IWG) , by means of scaffolds built from 16.01 m up to 18.00 m
IWG-07 Walkways (WG) 20 18.01-20 Installation of Walkways (IWG) , by means of scaffolds built from 18.01 m up to 20.00 m
IWG-08 Walkways (WG) 22 20.01-22 Installation of Walkways (IWG) , by means of scaffolds built from 20.01 m up to 22.00 m
IWG-09 Walkways (WG) 24 22.01-24 Installation of Walkways (IWG) , by means of scaffolds built from 22.01 m up to 24.00 m
IWG-10 Walkways (WG) 26 24.01-26 Installation of Walkways (IWG) , by means of scaffolds built from 24.01 m up to 26.00 m
IWG-11 Walkways (WG) 28 26.01-28 Installation of Walkways (IWG) , by means of scaffolds built from 26.01 m up to 28.00 m
IWG-12 Walkways (WG) 30 28.01-30 Installation of Walkways (IWG) , by means of scaffolds built from 28.01 m up to 30.00 m
IWG-13 Walkways (WG) 32 30.01-32 Installation of Walkways (IWG) , by means of scaffolds built from 30.01 m up to 32.00 m
IWG-14 Walkways (WG) 34 32.01-34 Installation of Walkways (IWG) , by means of scaffolds built from 32.01 m up to 34.00 m
IWG-15 Walkways (WG) 36 34.01-36 Installation of Walkways (IWG) , by means of scaffolds built from 34.01 m up to 36.00 m
IWG-16 Walkways (WG) 38 36.01-38 Installation of Walkways (IWG) , by means of scaffolds built from 36.01 m up to 38.00 m
IWG-17 Walkways (WG) 40 38.01-40 Installation of Walkways (IWG) , by means of scaffolds built from 38.01 m up to 40.00 m
IWG-18 Walkways (WG) 42 40.01-42 Installation of Walkways (IWG) , by means of scaffolds built from 40.01 m up to 42.00 m
IWG-19 Walkways (WG) 44 42.01-44 Installation of Walkways (IWG) , by means of scaffolds built from 42.01 m up to 44.00 m
IWG-20 Walkways (WG) 46 44.01-46 Installation of Walkways (IWG) , by means of scaffolds built from 44.01 m up to 46.00 m
IWG-21 Walkways (WG) 48 46.01-48 Installation of Walkways (IWG) , by means of scaffolds built from 46.01 m up to 48.00 m
IWG-22 Walkways (WG) 50 48.01-50 Installation of Walkways (IWG) , by means of scaffolds built from 48.01 m up to 50.00 m
IWG-23 Walkways (WG) 51 50.01-Above Installation of Walkways (IWG) , by means of scaffolds built above 50 m

ISF-01 Suspended Floors (SF) 8 6.01-8 Installation of Suspended Floors (ISF) from from 6.01 m up to 8.00 m
ISF-02 Suspended Floors (SF) 10 8.01-10 Installation of Suspended Floors (ISF) from from 8.01 m up to 10.00 m
ISF-03 Suspended Floors (SF) 12 10.01-12 Installation of Suspended Floors (ISF) from from 10.01 m up to 12.00 m
ISF-04 Suspended Floors (SF) 14 12.01-14 Installation of Suspended Floors (ISF) from from 12.01 m up to 14.00 m
ISF-05 Suspended Floors (SF) 16 14.01-16 Installation of Suspended Floors (ISF) from from 14.01 m up to 16.00 m
ISF-06 Suspended Floors (SF) 18 16.01-18 Installation of Suspended Floors (ISF) from from 16.01 m up to 18.00 m
ISF-07 Suspended Floors (SF) 20 18.01-20 Installation of Suspended Floors (ISF) from from 18.01 m up to 20.00 m
ISF-08 Suspended Floors (SF) 22 20.01-22 Installation of Suspended Floors (ISF) from from 20.01 m up to 22.00 m
ISF-09 Suspended Floors (SF) 24 22.01-24 Installation of Suspended Floors (ISF) from from 22.01 m up to 24.00 m
ISF-10 Suspended Floors (SF) 26 24.01-26 Installation of Suspended Floors (ISF) from from 24.01 m up to 26.00 m
ISF-11 Suspended Floors (SF) 28 26.01-28 Installation of Suspended Floors (ISF) from from 26.01 m up to 28.00 m
ISF-12 Suspended Floors (SF) 30 28.01-30 Installation of Suspended Floors (ISF) from from 28.01 m up to 30.00 m
ISF-13 Suspended Floors (SF) 32 30.01-32 Installation of Suspended Floors (ISF) from from 30.01 m up to 32.00 m
ISF-14 Suspended Floors (SF) 34 32.01-34 Installation of Suspended Floors (ISF) from from 32.01 m up to 34.00 m
ISF-15 Suspended Floors (SF) 36 34.01-36 Installation of Suspended Floors (ISF) from from 34.01 m up to 36.00 m
ISF-16 Suspended Floors (SF) 38 36.01-38 Installation of Suspended Floors (ISF) from from 36.01 m up to 38.00 m
ISF-17 Suspended Floors (SF) 40 38.01-40 Installation of Suspended Floors (ISF) from from 38.01 m up to 40.00 m
ISF-18 Suspended Floors (SF) 42 40.01-42 Installation of Suspended Floors (ISF) from from 40.01 m up to 42.00 m
ISF-19 Suspended Floors (SF) 44 42.01-44 Installation of Suspended Floors (ISF) from from 42.01 m up to 44.00 m
ISF-20 Suspended Floors (SF) 46 44.01-46 Installation of Suspended Floors (ISF) from from 44.01 m up to 46.00 m
ISF-21 Suspended Floors (SF) 48 46.01-48 Installation of Suspended Floors (ISF) from from 46.01 m up to 48.00 m
ISF-22 Suspended Floors (SF) 50 48.01-50 Installation of Suspended Floors (ISF) from from 48.01 m up to 50.00 m
ISF-23 Suspended Floors (SF) 51 50.01-Above Installation of Suspended Floors (ISF) above 50 m

ITS-01 Tower Structures (TS) 6 0-6 Installation of Tower Structures (ITS) from ground up to 6.00 m
ITS-02 Tower Structures (TS) 8 6.0-8 Installation of Tower Structures (ITS) from 6.01 m up to 8.00 m
ITS-03 Tower Structures (TS) 10 8.01-10 Installation of Tower Structures (ITS) from 8.01 m up to 10.00 m
ITS-04 Tower Structures (TS) 12 10.01-12 Installation of Tower Structures (ITS) from 10.01 m up to 12.00 m
ITS-05 Tower Structures (TS) 14 12.01-14 Installation of Tower Structures (ITS) from 12.01 m up to 14.00 m
ITS-06 Tower Structures (TS) 16 14.01-16 Installation of Tower Structures (ITS) from 14.01 m up to 16.00 m
ITS-07 Tower Structures (TS) 18 16.01-18 Installation of Tower Structures (ITS) from 16.01 m up to 18.00 m
ITS-08 Tower Structures (TS) 20 18.01-20 Installation of Tower Structures (ITS) from 18.01 m up to 20.00 m
ITS-09 Tower Structures (TS) 22 20.01-22 Installation of Tower Structures (ITS) from 20.01 m up to 22.00 m
ITS-10 Tower Structures (TS) 24 22.01-24 Installation of Tower Structures (ITS) from 22.01 m up to 24.00 m
ITS-11 Tower Structures (TS) 26 24.01-26 Installation of Tower Structures (ITS) from 24.01 m up to 26.00 m
ITS-12 Tower Structures (TS) 28 26.01-28 Installation of Tower Structures (ITS) from 26.01 m up to 28.00 m
ITS-13 Tower Structures (TS) 30 28.01-30 Installation of Tower Structures (ITS) from 28.01 m up to 30.00 m
ITS-14 Tower Structures (TS) 32 30.01-32 Installation of Tower Structures (ITS) from 30.01 m up to 32.00 m
ITS-15 Tower Structures (TS) 34 32.01-34 Installation of Tower Structures (ITS) from 32.01 m up to 34.00 m
ITS-16 Tower Structures (TS) 36 34.01-36 Installation of Tower Structures (ITS) from 34.01 m up to 36.00 m
ITS-17 Tower Structures (TS) 38 36.01-38 Installation of Tower Structures (ITS) from 36.01 m up to 38.00 m
ITS-18 Tower Structures (TS) 40 38.01-40 Installation of Tower Structures (ITS) from 38.01 m up to 40.00 m
ITS-19 Tower Structures (TS) 42 40.01-42 Installation of Tower Structures (ITS) from 40.01 m up to 42.00 m
ITS-20 Tower Structures (TS) 44 42.01-44 Installation of Tower Structures (ITS) from 42.01 m up to 44.00 m
ITS-21 Tower Structures (TS) 46 44.01-46 Installation of Tower Structures (ITS) from 44.01 m up to 46.00 m
ITS-22 Tower Structures (TS) 48 46.01-48 Installation of Tower Structures (ITS) from 46.01 m up to 48.00 m
ITS-23 Tower Structures (TS) 50 48.01-50 Installation of Tower Structures (ITS) from 48.01 m up to 50.00 m
ITS-24 Tower Structures (TS) 51 50.01-Above Installation of Tower Structures (ITS) above 50 m

IBC-01 Birdcages (IBC) 6 0-6.0 Installation of Birdcages (IBC) from ground up to 6.00 m
IBC-02 Birdcages (IBC) 8 6.01-8 Installation of Birdcages (IBC) from 6.01 m up to 8.00 m
IBC-03 Birdcages (IBC) 10 8.01-10 Installation of Birdcages (IBC) from 8.01 m up to 10.00 m
IBC-04 Birdcages (IBC) 12 10.01-12 Installation of Birdcages (IBC) from 10.01 m up to 12.00 m
IBC-05 Birdcages (IBC) 14 12.01-14 Installation of Birdcages (IBC) from 12.01 m up to 14.00 m
IBC-06 Birdcages (IBC) 16 14.01-16 Installation of Birdcages (IBC) from 14.01 m up to 16.00 m
IBC-07 Birdcages (IBC) 18 16.01-18 Installation of Birdcages (IBC) from 16.01 m up to 18.00 m
IBC-08 Birdcages (IBC) 20 18.01-20 Installation of Birdcages (IBC) from 18.01 m up to 20.00 m
IBC-09 Birdcages (IBC) 22 20.01-22 Installation of Birdcages (IBC) from 20.01 m up to 22.00 m
IBC-10 Birdcages (IBC) 24 22.01-24 Installation of Birdcages (IBC) from 22.01 m up to 24.00 m
IBC-11 Birdcages (IBC) 26 24.01-26 Installation of Birdcages (IBC) from 24.01 m up to 26.00 m
IBC-12 Birdcages (IBC) 28 26.01-28 Installation of Birdcages (IBC) from 26.01 m up to 28.00 m
IBC-13 Birdcages (IBC) 30 28.01-30 Installation of Birdcages (IBC) from 28.01 m up to 30.00 m
IBC-14 Birdcages (IBC) 32 30.01-32 Installation of Birdcages (IBC) from 30.01 m up to 32.00 m
IBC-15 Birdcages (IBC) 34 32.01-34 Installation of Birdcages (IBC) from 32.01 m up to 34.00 m
IBC-16 Birdcages (IBC) 36 34.01-36 Installation of Birdcages (IBC) from 34.01 m up to 36.00 m
IBC-17 Birdcages (IBC) 38 36.01-38 Installation of Birdcages (IBC) from 36.01 m up to 38.00 m
IBC-18 Birdcages (IBC) 40 38.01-40 Installation of Birdcages (IBC) from 38.01 m up to 40.00 m
IBC-19 Birdcages (IBC) 42 40.01-42 Installation of Birdcages (IBC) from 40.01 m up to 42.00 m
IBC-20 Birdcages (IBC) 44 42.01-44 Installation of Birdcages (IBC) from 42.01 m up to 44.00 m
IBC-21 Birdcages (IBC) 46 44.01-46 Installation of Birdcages (IBC) from 44.01 m up to 46.00 m
IBC-22 Birdcages (IBC) 48 46.01-48 Installation of Birdcages (IBC) from 46.01 m up to 48.00 m
IBC-23 Birdcages (IBC) 50 48.01-50 Installation of Birdcages (IBC) from 48.01 m up to 50.00 m
IBC-24 Birdcages (IBC) 51 50.01-Above Installation of Birdcages (IBC) above 50 m

IHBC-01 Hanging Birdcages (IHBC) 2 0-2 Installation of Hanging Birdcages (IHBC) from structure down to 2.00 m
IHBC-02 Hanging Birdcages (IHBC) 4 2.01-4 Installation of Hanging Birdcages (IHBC) from structure down from to 2.01 m to 4.00 m
IHBC-03 Hanging Birdcages (IHBC) 6 4.01-6 Installation of Hanging Birdcages (IHBC) from structure down from to 4.01 m to 6.00 m

IIN-01 Independents (IN) 6 0 - 6.0 Installation of Independents (IIN) from ground up to 6.00 m
IIN-02 Independents (IN) 8 6.01-8 Installation of Independents (IIN) from 6.01 m up to 8.00 m
IIN-03 Independents (IN) 10 8.01-10 Installation of Independents (IIN) from 8.01 m up to 10.00 m
IIN-04 Independents (IN) 12 10.01-12 Installation of Independents (IIN) from 10.01 m up to 12.00 m
IIN-05 Independents (IN) 14 12.01-14 Installation of Independents (IIN) from 12.01 m up to 14.00 m
IIN-06 Independents (IN) 16 14.01-16 Installation of Independents (IIN) from 14.01 m up to 16.00 m
IIN-07 Independents (IN) 18 16.01-18 Installation of Independents (IIN) from 16.01 m up to 18.00 m
IIN-08 Independents (IN) 20 18.01-20 Installation of Independents (IIN) from 18.01 m up to 20.00 m
IIN-09 Independents (IN) 22 20.01-22 Installation of Independents (IIN) from 20.01 m up to 22.00 m
IIN-10 Independents (IN) 24 22.01-24 Installation of Independents (IIN) from 22.01 m up to 24.00 m
IIN-11 Independents (IN) 26 24.01-26 Installation of Independents (IIN) from 24.01 m up to 26.00 m
IIN-12 Independents (IN) 28 26.01-28 Installation of Independents (IIN) from 26.01 m up to 28.00 m
IIN-13 Independents (IN) 30 28.01-30 Installation of Independents (IIN) from 28.01 m up to 30.00 m
IIN-14 Independents (IN) 32 30.01-32 Installation of Independents (IIN) from 30.01 m up to 32.00 m
IIN-15 Independents (IN) 34 32.01-34 Installation of Independents (IIN) from 32.01 m up to 34.00 m
IIN-16 Independents (IN) 36 34.01-36 Installation of Independents (IIN) from 34.01 m up to 36.00 m
IIN-17 Independents (IN) 38 36.01-38 Installation of Independents (IIN) from 36.01 m up to 38.00 m
IIN-18 Independents (IN) 40 38.01-40 Installation of Independents (IIN) from 38.01 m up to 40.00 m
IIN-19 Independents (IN) 42 40.01-42 Installation of Independents (IIN) from 40.01 m up to 42.00 m
IIN-20 Independents (IN) 44 42.01-44 Installation of Independents (IIN) from 42.01 m up to 44.00 m
IIN-21 Independents (IN) 46 44.01-46 Installation of Independents (IIN) from 44.01 m up to 46.00 m
IIN-22 Independents (IN) 48 46.01-48 Installation of Independents (IIN) from 46.01 m up to 48.00 m
IIN-23 Independents (IN) 50 48.01-50 Installation of Independents (IIN) from 48.01 m up to 50.00 m
IIN-24 Independents (IN) 51 50.01-Above Installation of Independents (IIN) above 50 m
 
Sort by date Sort by votes

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
and this is not any formula i gave you:
IF(AND(VALUE(I9)>0,VALUE(I9)<=6),"6",IF(AND(VALUE(I9)>=6.01,VALUE(I9)<=8),8,IF(AND(VALUE(I9)>=8.01,VALUE(I9)<=10),10,IF(AND(VALUE(I9)>=10.01,VALUE(I9)<=12),12,IF(AND(VALUE(I9)>=12.01,VALUE(I9)<=14),14,IF(AND(VALUE(I9)>=14.01,VALUE(I9)<=16),16,IF(AND(VALUE(I9)>=16.01,VALUE(I9)<=18),18,IF(AND(VALUE(I9)>=18.01,VALUE(I9)<=20),20,IF(AND(VALUE(I9)>=20.01,VALUE(I9)<=22),22,IF(AND(VALUE(I9)>=22.01,VALUE(I9)<=24),24,IF(AND(VALUE(I9)>=24.01,VALUE(I9)<=26),26,IF(AND(VALUE(I9)>=26.01,VALUE(I9)<=28),28,IF(AND(VALUE(I9)>=28.01,VALUE(I9)<=30),30,IF(AND(VALUE(I9)>=30.01,VALUE(I9)<=32),32,IF(AND(VALUE(I9)>=32.01,VALUE(I9)<=34),34,IF(AND(VALUE(I9)>=36.01,VALUE(I9)<=36),36,IF(AND(VALUE(I9)>=36.01,VALUE(I9)<=38),38,IF(AND(VALUE(I9)>=38.01,VALUE(I9)<=40),40,IF(AND(VALUE(I9)>=40.01,VALUE(I9)<=42),42,IF(AND(VALUE(I9)>=42.01,VALUE(I9)<=44),44,IF(AND(VALUE(I9)>=44.01,VALUE(I9)<=46),46,IF(AND(VALUE(I9)>=46.01,VALUE(I9)<=48),48,IF(AND(VALUE(I9)>=48.01,VALUE(I9)<=50),50,IF(AND(VALUE(I9)>=50.01,VALUE(I9)<=51),51,"")))))))))))))))))))))))))))
Click to expand...
Why did you tell me you had an error with my formula earlier when you posted data that was not even using it?

And you do not need to paste your entire data source. I have it! And it is in prior posts.
What I want is something like this:

CRITERIA:
Something like this:
Please create a formula that returns all records where this is true:
Return all records where TYPE Is "BIRDCAGES (IBC)" and the Height ( of 4) is between the Height Range.
(Have I missed anything?,... if so put it here).
ITEM NO.TypeJobHeight RangeHeight TypeDESCRIPTION
BIRDCAGES (IBC)4
 
Upvote 0
See if this does what you want. I have only shown a part of the large data and you may need to adjust the range in the formula.

nazarene.xlsm
ABCDEF
1ITEM NO.TypeJobHeight RangeHeight TypeDESCRIPTION
2STStair Towers (ST)
3IST-01Stair Towers (ST)Install0-66Installation of Stair Towers (IST) from ground up to 6.00 m
4IST-02Stair Towers (ST)Install6.01-88Installation of Stair Towers (IST) from 6.01 m up to 8.00 m
5IST-03Stair Towers (ST)Install8.01-1010Installation of Stair Towers (IST) from 8.01 m up to 10.00 m
6IST-04Stair Towers (ST)Install10.01-1212Installation of Stair Towers (IST) from 10.01 m up to 12.00 m
7IST-05Stair Towers (ST)Install12.01-1414Installation of Stair Towers (IST) from 12.01 m up to 14.00 m
8IST-06Stair Towers (ST)Install14.01-1616Installation of Stair Towers (IST) from 14.01 m up to 16.00 m
9IST-07Stair Towers (ST)Install16.01-1818Installation of Stair Towers (IST) from 16.01 m up to 18.00 m
10IST-08Stair Towers (ST)Install18.01-2020Installation of Stair Towers (IST) from 18.01 m up to 20.00 m
11IST-09Stair Towers (ST)Install20.01-2222Installation of Stair Towers (IST) from 20.01 m up to 22.00 m
12IST-10Stair Towers (ST)Install22.01-2424Installation of Stair Towers (IST) from 22.01 m up to 24.00 m
13IST-11Stair Towers (ST)Install24.01-2626Installation of Stair Towers (IST) from 24.01 m up to 26.00 m
14IST-12Stair Towers (ST)Install26.01-2828Installation of Stair Towers (IST) from 26.01 m up to 28.00 m
15IST-13Stair Towers (ST)Install28.01-3030Installation of Stair Towers (IST) from 28.01 m up to 30.00 m
16IST-14Stair Towers (ST)Install30.01-3232Installation of Stair Towers (IST) from 30.01 m up to 32.00 m
17IST-15Stair Towers (ST)Install32.01-3434Installation of Stair Towers (IST) from 32.01 m up to 34.00 m
18IST-16Stair Towers (ST)Install34.01-3636Installation of Stair Towers (IST) from 34.01 m up to 36.00 m
19IST-17Stair Towers (ST)Install36.01-3838Installation of Stair Towers (IST) from 36.01 m up to 38.00 m
20IST-18Stair Towers (ST)Install38.01-4040Installation of Stair Towers (IST) from 38.01 m up to 40.00 m
21IST-19Stair Towers (ST)Install40.01-4242Installation of Stair Towers (IST) from 40.01 m up to 42.00 m
22IST-20Stair Towers (ST)Install42.01-4444Installation of Stair Towers (IST) from 42.01 m up to 44.00 m
23IST-21Stair Towers (ST)Install44.01-4646Installation of Stair Towers (IST) from 44.01 m up to 46.00 m
24IST-22Stair Towers (ST)Install46.01-4848Installation of Stair Towers (IST) from 46.01 m up to 48.00 m
25IST-23Stair Towers (ST)Install48.01-5050Installation of Stair Towers (IST) from 48.01 m up to 50.00 m
26IST-23Stair Towers (ST)Install50.01- Above51Installation of Stair Towers (IST) above 50 m
27WGWalkways (WG)
28IWG-01Walkways (WG)Install6.01-88Installation of Walkways (IWG) , by means of scaffolds built from 6.01 m up to 8.00 m
29IWG-02Walkways (WG)Install8.01-1010Installation of Walkways (IWG) , by means of scaffolds built from 8.01 m up to 10.00 m
30IWG-03Walkways (WG)Install10.01-1212Installation of Walkways (IWG) , by means of scaffolds built from 10.01 m up to 12.00 m
31IWG-04Walkways (WG)Install12.01-1414Installation of Walkways (IWG) , by means of scaffolds built from 12.01 m up to 14.00 m
32IWG-05Walkways (WG)Install14.01-1616Installation of Walkways (IWG) , by means of scaffolds built from 14.01 m up to 16.00 m
33IWG-06Walkways (WG)Install16.01-1818Installation of Walkways (IWG) , by means of scaffolds built from 16.01 m up to 18.00 m
34IWG-07Walkways (WG)Install18.01-2020Installation of Walkways (IWG) , by means of scaffolds built from 18.01 m up to 20.00 m
35IWG-08Walkways (WG)Install20.01-2222Installation of Walkways (IWG) , by means of scaffolds built from 20.01 m up to 22.00 m
36IWG-09Walkways (WG)Install22.01-2424Installation of Walkways (IWG) , by means of scaffolds built from 22.01 m up to 24.00 m
37IWG-10Walkways (WG)Install24.01-2626Installation of Walkways (IWG) , by means of scaffolds built from 24.01 m up to 26.00 m
38IWG-11Walkways (WG)Install26.01-2828Installation of Walkways (IWG) , by means of scaffolds built from 26.01 m up to 28.00 m
39IWG-12Walkways (WG)Install28.01-3030Installation of Walkways (IWG) , by means of scaffolds built from 28.01 m up to 30.00 m
40IWG-13Walkways (WG)Install30.01-3232Installation of Walkways (IWG) , by means of scaffolds built from 30.01 m up to 32.00 m
41IWG-14Walkways (WG)Install32.01-3434Installation of Walkways (IWG) , by means of scaffolds built from 32.01 m up to 34.00 m
42IWG-15Walkways (WG)Install34.01-3636Installation of Walkways (IWG) , by means of scaffolds built from 34.01 m up to 36.00 m
43IWG-16Walkways (WG)Install36.01-3838Installation of Walkways (IWG) , by means of scaffolds built from 36.01 m up to 38.00 m
44IWG-17Walkways (WG)Install38.01-4040Installation of Walkways (IWG) , by means of scaffolds built from 38.01 m up to 40.00 m
45IWG-18Walkways (WG)Install40.01-4242Installation of Walkways (IWG) , by means of scaffolds built from 40.01 m up to 42.00 m
46IWG-19Walkways (WG)Install42.01-4444Installation of Walkways (IWG) , by means of scaffolds built from 42.01 m up to 44.00 m
47IWG-20Walkways (WG)Install44.01-4646Installation of Walkways (IWG) , by means of scaffolds built from 44.01 m up to 46.00 m
48IWG-21Walkways (WG)Install46.01-4848Installation of Walkways (IWG) , by means of scaffolds built from 46.01 m up to 48.00 m
49IWG-22Walkways (WG)Install48.01-5050Installation of Walkways (IWG) , by means of scaffolds built from 48.01 m up to 50.00 m
50IWG-23Walkways (WG)Install50.01-Above51Installation of Walkways (IWG) , by means of scaffolds built above 50 m
Sheet1


nazarene.xlsm
ABC
11. Scafolding Description2. Height Meter3. Height type
2
3Independents (IN)16
4Stair Towers (ST)31.532
5Stair Towers (ST)6051
6Walkways (WG)1212
Sheet2
Cell Formulas
RangeFormula
C3:C6C3=LET(n,FILTER(Sheet1!E$3:E$161,Sheet1!B$3:B$161=A3),XLOOKUP(B3,n,n,MAX(n),1))
 
Upvote 0
Solution
awoohaw said:
and this is not any formula i gave you:

Why did you tell me you had an error with my formula earlier when you posted data that was not even using it?

And you do not need to paste your entire data source. I have it! And it is in prior posts.
What I want is something like this:

CRITERIA:
Something like this:
Please create a formula that returns all records where this is true:
Return all records where TYPE Is "BIRDCAGES (IBC)" and the Height ( of 4) is between the Height Range.
(Have I missed anything?,... if so put it here).
ITEM NO.TypeJobHeight RangeHeight TypeDESCRIPTION
BIRDCAGES (IBC)4
Click to expand...
Thank you sir it is ok now
 
Upvote 0
Peter_SSs said:
See if this does what you want. I have only shown a part of the large data and you may need to adjust the range in the formula.

nazarene.xlsm
ABCDEF
1ITEM NO.TypeJobHeight RangeHeight TypeDESCRIPTION
2STStair Towers (ST)
3IST-01Stair Towers (ST)Install0-66Installation of Stair Towers (IST) from ground up to 6.00 m
4IST-02Stair Towers (ST)Install6.01-88Installation of Stair Towers (IST) from 6.01 m up to 8.00 m
5IST-03Stair Towers (ST)Install8.01-1010Installation of Stair Towers (IST) from 8.01 m up to 10.00 m
6IST-04Stair Towers (ST)Install10.01-1212Installation of Stair Towers (IST) from 10.01 m up to 12.00 m
7IST-05Stair Towers (ST)Install12.01-1414Installation of Stair Towers (IST) from 12.01 m up to 14.00 m
8IST-06Stair Towers (ST)Install14.01-1616Installation of Stair Towers (IST) from 14.01 m up to 16.00 m
9IST-07Stair Towers (ST)Install16.01-1818Installation of Stair Towers (IST) from 16.01 m up to 18.00 m
10IST-08Stair Towers (ST)Install18.01-2020Installation of Stair Towers (IST) from 18.01 m up to 20.00 m
11IST-09Stair Towers (ST)Install20.01-2222Installation of Stair Towers (IST) from 20.01 m up to 22.00 m
12IST-10Stair Towers (ST)Install22.01-2424Installation of Stair Towers (IST) from 22.01 m up to 24.00 m
13IST-11Stair Towers (ST)Install24.01-2626Installation of Stair Towers (IST) from 24.01 m up to 26.00 m
14IST-12Stair Towers (ST)Install26.01-2828Installation of Stair Towers (IST) from 26.01 m up to 28.00 m
15IST-13Stair Towers (ST)Install28.01-3030Installation of Stair Towers (IST) from 28.01 m up to 30.00 m
16IST-14Stair Towers (ST)Install30.01-3232Installation of Stair Towers (IST) from 30.01 m up to 32.00 m
17IST-15Stair Towers (ST)Install32.01-3434Installation of Stair Towers (IST) from 32.01 m up to 34.00 m
18IST-16Stair Towers (ST)Install34.01-3636Installation of Stair Towers (IST) from 34.01 m up to 36.00 m
19IST-17Stair Towers (ST)Install36.01-3838Installation of Stair Towers (IST) from 36.01 m up to 38.00 m
20IST-18Stair Towers (ST)Install38.01-4040Installation of Stair Towers (IST) from 38.01 m up to 40.00 m
21IST-19Stair Towers (ST)Install40.01-4242Installation of Stair Towers (IST) from 40.01 m up to 42.00 m
22IST-20Stair Towers (ST)Install42.01-4444Installation of Stair Towers (IST) from 42.01 m up to 44.00 m
23IST-21Stair Towers (ST)Install44.01-4646Installation of Stair Towers (IST) from 44.01 m up to 46.00 m
24IST-22Stair Towers (ST)Install46.01-4848Installation of Stair Towers (IST) from 46.01 m up to 48.00 m
25IST-23Stair Towers (ST)Install48.01-5050Installation of Stair Towers (IST) from 48.01 m up to 50.00 m
26IST-23Stair Towers (ST)Install50.01- Above51Installation of Stair Towers (IST) above 50 m
27WGWalkways (WG)
28IWG-01Walkways (WG)Install6.01-88Installation of Walkways (IWG) , by means of scaffolds built from 6.01 m up to 8.00 m
29IWG-02Walkways (WG)Install8.01-1010Installation of Walkways (IWG) , by means of scaffolds built from 8.01 m up to 10.00 m
30IWG-03Walkways (WG)Install10.01-1212Installation of Walkways (IWG) , by means of scaffolds built from 10.01 m up to 12.00 m
31IWG-04Walkways (WG)Install12.01-1414Installation of Walkways (IWG) , by means of scaffolds built from 12.01 m up to 14.00 m
32IWG-05Walkways (WG)Install14.01-1616Installation of Walkways (IWG) , by means of scaffolds built from 14.01 m up to 16.00 m
33IWG-06Walkways (WG)Install16.01-1818Installation of Walkways (IWG) , by means of scaffolds built from 16.01 m up to 18.00 m
34IWG-07Walkways (WG)Install18.01-2020Installation of Walkways (IWG) , by means of scaffolds built from 18.01 m up to 20.00 m
35IWG-08Walkways (WG)Install20.01-2222Installation of Walkways (IWG) , by means of scaffolds built from 20.01 m up to 22.00 m
36IWG-09Walkways (WG)Install22.01-2424Installation of Walkways (IWG) , by means of scaffolds built from 22.01 m up to 24.00 m
37IWG-10Walkways (WG)Install24.01-2626Installation of Walkways (IWG) , by means of scaffolds built from 24.01 m up to 26.00 m
38IWG-11Walkways (WG)Install26.01-2828Installation of Walkways (IWG) , by means of scaffolds built from 26.01 m up to 28.00 m
39IWG-12Walkways (WG)Install28.01-3030Installation of Walkways (IWG) , by means of scaffolds built from 28.01 m up to 30.00 m
40IWG-13Walkways (WG)Install30.01-3232Installation of Walkways (IWG) , by means of scaffolds built from 30.01 m up to 32.00 m
41IWG-14Walkways (WG)Install32.01-3434Installation of Walkways (IWG) , by means of scaffolds built from 32.01 m up to 34.00 m
42IWG-15Walkways (WG)Install34.01-3636Installation of Walkways (IWG) , by means of scaffolds built from 34.01 m up to 36.00 m
43IWG-16Walkways (WG)Install36.01-3838Installation of Walkways (IWG) , by means of scaffolds built from 36.01 m up to 38.00 m
44IWG-17Walkways (WG)Install38.01-4040Installation of Walkways (IWG) , by means of scaffolds built from 38.01 m up to 40.00 m
45IWG-18Walkways (WG)Install40.01-4242Installation of Walkways (IWG) , by means of scaffolds built from 40.01 m up to 42.00 m
46IWG-19Walkways (WG)Install42.01-4444Installation of Walkways (IWG) , by means of scaffolds built from 42.01 m up to 44.00 m
47IWG-20Walkways (WG)Install44.01-4646Installation of Walkways (IWG) , by means of scaffolds built from 44.01 m up to 46.00 m
48IWG-21Walkways (WG)Install46.01-4848Installation of Walkways (IWG) , by means of scaffolds built from 46.01 m up to 48.00 m
49IWG-22Walkways (WG)Install48.01-5050Installation of Walkways (IWG) , by means of scaffolds built from 48.01 m up to 50.00 m
50IWG-23Walkways (WG)Install50.01-Above51Installation of Walkways (IWG) , by means of scaffolds built above 50 m
Sheet1


nazarene.xlsm
ABC
11. Scafolding Description2. Height Meter3. Height type
2
3Independents (IN)16
4Stair Towers (ST)31.532
5Stair Towers (ST)6051
6Walkways (WG)1212
Sheet2
Cell Formulas
RangeFormula
C3:C6C3=LET(n,FILTER(Sheet1!E$3:E$161,Sheet1!B$3:B$161=A3),XLOOKUP(B3,n,n,MAX(n),1))
Click to expand...
Thank you sir, your formula works
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,706
Messages
6,173,998
Members
452,542
Latest member
Bricklin

Share this page

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