IFS statement and Lookup Function??

[researchguru]

Hi there,

I'm trying to work out the 'rise of floor' of certain buildings on my Excel spreadsheet. As all buildings have different number of floors, I'm trying to come up with a formula based on what the subject level is, it categorises it as a low, mid, or high-rise floor based on the criteria listed in SHEET 2 (second spreadsheet/tab).

I've tried combining an IFS and VLOOKUP formula but no luck so far. I want to utilise a formula that will allow me to click and drag as I will have a big list to analyse.

I've attached an images for reference.

Many thanks in advance.

 

[kweaver]

Welcome.
Pictures don't help anyone trying to replicate your data. Please use XL2BB to show your sheet and expected result(s).

 

[researchguru]

Welcome.
Pictures don't help anyone trying to replicate your data. Please use XL2BB to show your sheet and expected result(s).

Apologies. See below

Workbook 1.xlsx
	B	C	D
3	Address	Floor/Level	Rise Of Floor
4	123 Example St	2
5	123 Example St	5
6	123 Example St	8
7	500 Test St	10
8	500 Test St	15
9
10
11	Please refer to 'Building List' tab for more information
12
Sheet 1

Workbook 1.xlsx
	B	C	D	E	F
1	Address	Total Floors	Low	Mid	High
2	123 Example St	10	3	7	10
3	500 Test St	20	6	13	20
4
5
6	For 123 Example St, if floor level indicated in Sheet 1, Cell C4,C5,C6 is: Less or equal to 3 = Low Between 4 to 7 = Mid Between 8 to 10 = High
7
8
9
10
11
12	For 500 Test St, if floor level indicated in Sheet 1, Cell C7 and C8 is: Less or equal to 6 = Low Between 7 to 13 = Mid Between 14 to 20 = High
13
14
15
16
Building List

 

[kweaver]

(a) how do you know how many floors there are?
(b) what's the logic to know, for example, that123 Example is low, medium or high?

 

[Alex Blakenburg]

See if this works for you.

20230619 2 Way XLookup Approx match researchguru.xlsx
	A	B	C	D
1
2
3		Address	Floor	Rise
4		123 Example St	2	Low
5		123 Example St	5	Mid
6		123 Example St	8	High
7		500 Test St	10	Mid
8		500 Test St	15	High
Sheet1

Cell Formulas
Range	Formula
D4:D8	D4	=XLOOKUP(C4, XLOOKUP(B4, Sheet2!$B$2:$B$3, Sheet2!$D$2:$F$3), Sheet2!$D$1:$F$1,"",1,1)

 

[researchguru]

See if this works for you.

20230619 2 Way XLookup Approx match researchguru.xlsx
	A	B	C	D
1
2
3		Address	Floor	Rise
4		123 Example St	2	Low
5		123 Example St	5	Mid
6		123 Example St	8	High
7		500 Test St	10	Mid
8		500 Test St	15	High
Sheet1

Cell Formulas
Range	Formula
D4:D8	D4	=XLOOKUP(C4, XLOOKUP(B4, Sheet2!$B$2:$B$3, Sheet2!$D$2:$F$3), Sheet2!$D$1:$F$1,"",1,1)

This worked perfectly! Thank you Alex.

 

[Alex Blakenburg]

You're welcome. Glad we could help.