IFS statement and Lookup Function??

researchguru

New Member
Joined
Jun 18, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Image 1.PNG
    Image 1.PNG
    26.5 KB · Views: 8
  • Image 2.PNG
    Image 2.PNG
    54.1 KB · Views: 9

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Welcome.
Pictures don't help anyone trying to replicate your data. Please use XL2BB to show your sheet and expected result(s).
 
Upvote 0
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
BCD
3AddressFloor/LevelRise Of Floor
4123 Example St2
5123 Example St5
6123 Example St8
7500 Test St10
8500 Test St15
9
10
11Please refer to 'Building List' tab for more information
12
Sheet 1


Workbook 1.xlsx
BCDEF
1AddressTotal FloorsLowMidHigh
2123 Example St103710
3500 Test St2061320
4
5
6For 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
12For 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
 
Upvote 0
(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?
 
Upvote 0
See if this works for you.

20230619 2 Way XLookup Approx match researchguru.xlsx
ABCD
1
2
3AddressFloorRise
4123 Example St2Low
5123 Example St5Mid
6123 Example St8High
7500 Test St10Mid
8500 Test St15High
Sheet1
Cell Formulas
RangeFormula
D4:D8D4=XLOOKUP(C4, XLOOKUP(B4, Sheet2!$B$2:$B$3, Sheet2!$D$2:$F$3), Sheet2!$D$1:$F$1,"",1,1)
 
Upvote 2
See if this works for you.

20230619 2 Way XLookup Approx match researchguru.xlsx
ABCD
1
2
3AddressFloorRise
4123 Example St2Low
5123 Example St5Mid
6123 Example St8High
7500 Test St10Mid
8500 Test St15High
Sheet1
Cell Formulas
RangeFormula
D4:D8D4=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.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

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