If specific number then specific text

[jfire1973]

I'm building a spreadsheet to track how often we respond (fire Dept) to a certain hazard level facility. I'd like to be able when I put the building number say in cell A1 that in A2 it says whether it's Low, Moderate, or High risk.
A1 cell: 1285
A2 cell: Low

[etaf]

do you have a table of building numbers and risks ?

A sample sheet would help here

The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[jfire1973]

Sample Work Book.xlsx

See attached sample workbook

[etaf]

=INDEX('Building Numbers'!$B$2:$B$18,MATCH(January!F3,'Building Numbers'!$A$2:$A$18,0))

BUT

a few values are not in the table
872 RM 201
1409 RM 2533

so i see that seems to match on the numbers at the beginning

I think we need some form of explanation on what you expect excel to do

I have added the formula into column N just so you can compare against your manual result in column O

[jfire1973]

ETAF, you are amazing!!! That is exactly what we needed!!! One last question and if possible great if not this will work fine. Can I add and where would I add that if anything is column F is blank that N would be blank too instead of saying #N/A?

[etaf]

add an iferror()
=IFERROR(INDEX('Building Numbers'!$B$2:$B$18,MATCH(January!F3,'Building Numbers'!$A$2:$A$18,0)),"")

[jfire1973]

Thank you very much, this was awesome!!

[etaf]

you are welcome

[jfire1973]

Maxwell FES 2023 Run Data (Test).xls

One more formula, I hope...

I have a formula for counting how many Structural Responses that are low risk, that formula is in cell X3


in Cell X4, I need a formula that will average the time of the cells that come up as Structural Low risk.

Right now it's just two, rows 14 and 16, but there could be more later. Is there a way to average the times from Header (I) if when a row has structural selected in Header (D) and Low in Header (P)?

I hope this makes sense.

[etaf]

i get a lot of ref errors
but if you are after average
try
averageifs()

but i'm not following rows 14 and 15

the header in D says type and contents of the column include structural

=averageifs(I3:I52,D3:D52,"structural",P3:P52,"low")

you can use cell reference for the criteria Structural and low

[jfire1973]

Rows 14 and 16 were just ones that had the structural and Low, those were the examples, but the formula you have for the averageifs, is perfect!!! I can't thank you enough!!!

[jfire1973]

Cheers!!!! much appreciated!!!

[etaf]

you are welcome