Formula to look up a value form a chart

[SaraO]

Hello,

I have a chart set up as following... (Chart 1)

B C D E F G H I
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: center"]ML[/TD]
[TD="align: center"]Frame[/TD]
[TD="align: center"]Chassis 1[/TD]
[TD="align: center"]Chassis 2[/TD]
[TD="align: center"]Chassis 3[/TD]
[TD="align: center"]Chassis 4[/TD]
[TD="align: center"]Chassis 5[/TD]
[TD="align: center"]Chassis 6[/TD]
[/TR]
[TR]
[TD]ML1[/TD]
[TD]Frame 1[/TD]
[TD]1-576[/TD]
[TD]577-1152[/TD]
[TD]1153-1728[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ML2[/TD]
[TD]Frame 2[/TD]
[TD]1-576[/TD]
[TD]577-1152[/TD]
[TD]1153-1728[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ML3[/TD]
[TD]Frame 4[/TD]
[TD]1-576[/TD]
[TD]577-1152[/TD]
[TD]1153-1728[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ML4[/TD]
[TD]Frame 3[/TD]
[TD]1-576[/TD]
[TD]577-864[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ML5[/TD]
[TD]Frame 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1-576[/TD]
[TD]577-864[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ML6[/TD]
[TD]Frame 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1-576[/TD]
[TD]577-864[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 567"]
<tbody>[TR]
[TD]Another chart that looks like this... (Chart 2)
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Fiber #[/TD]
[TD="align: center"]ML[/TD]
[TD="align: center"]Frame[/TD]
[TD="align: center"]Chassis[/TD]
[TD="align: center"]Tray[/TD]
[TD="align: center"]Port[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In Chart 2 for "Fiber #" I enter a number between 1-1728, for "ML" I have a drop down list. When I select a ML from the drop down list it returns the corresponding "Frame" from Chart 1. This part I have figured out. Where I am having trouble is the "Chassis". I need a formula that return the corresponding Chassis the "Fiber #" falls in.

Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Fiber #[/TD]
[TD="align: center"]ML[/TD]
[TD="align: center"]Frame[/TD]
[TD="align: center"]Chassis[/TD]
[TD="align: center"]Tray[/TD]
[TD="align: center"]Port[/TD]
[/TR]
[TR]
[TD="align: center"]782[/TD]
[TD="align: center"]ML3[/TD]
[TD="align: center"]Frame 4[/TD]
[TD="align: center"]Chassis 2[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have a 3rd chart that returns the "Tray" & "Port" values, which I figured out as well. Just need help with the "Chassis".

 

[duggie33]

Hi SaraO,

In your example, how did you determine "Chassis 2" was the correct Chassis number to go with "ML3"? I looks like you could have picked that or "Chassis 1" or "Chassis 3" from "Chart 1".

Doug

 

[SaraO]

Hi Doug,

The "Fiber #" falls in the count range in "Chassis 2" for "ML 3".

Thank you.

 

[duggie33]

Oh, I see. I didn't follow that part. I will try to get back to you today with a formula that determine the correct Chassis number.

 

[duggie33]

SaraO,

With my limited Excel skills I can solve this with formulas but the formulas are very long mainly because the Chassis values (ie. 1-576) are strings and we are trying to determine if a number is in that range.

It is pretty easy to extract the numbers from that string using Left, Right and Length.
C1ML1min = IFERROR(LEFT(C1ML1,FIND("-",C1ML1)-1)*1,"")
C1ML1max = IFERROR(RIGHT(C1ML1,(LEN(C1ML1)-FIND("-",C1ML1)))*1,"")
………
C6ML6min = …
C6ML6max = …
That would be a total of 36 sets of those two formulas just to extract the ranges (min/max) for each Chassis/ML combination. Only six sets of those would be relevant to the ML number of each row in “Chart 2”.

After that you have to MATCH the ML and compare your Fiber # to the appropriate values to see if you are in between the min and max for each Chassis. A nested If statement with six Ifs…
For Chassis 1 =IF(AND(C1ML3min<>"",Fiber #=MEDIAN(Fiber #,C1ML3min,C1ML3max)),"Chassis 1",***five more similar Ifs***))))))
So replace each instance C1ML3min and C1ML3max with a formula similar to above and this gets pretty big and confusing to try to decipher later. It would be a monster…I have created a few of these over the years and regretted it later when I need to make changes. Breaking them into smaller formulas stored in other cells would probably be better than one long formula.

It could probably be reduced some if your data in “Chart 1” was changed. If it could be assumed that lower Chassis values were always to the left and they ascended to the right, you might be able to put a single number in each cell. For instance only the max number for each Chassis (ie. for ML1…Chassis 1 - 572, Chassis 2 - 1152, Chassis 3 – 1728). The min for Chassis 2 would be assumed to be greater than the max of Chassis 1.

There are some incredibly smart people that frequent this forum and many of them probably know ways to significantly reduce the length of the formulas to do the same thing. I am working with what I know and always trying to learn better and/or more efficient ways.

Phewww…all that being said, I think this could be done easier in VBA by looping through “Chart 1” to match the ML and Chassis information. I do not know if VBA is an option for your spreadsheet or your level of knowledge setting up macros so that may determine how you want to proceed.

Wish I could be of more immediate help,

Doug

 

[SaraO]

Hi Doug,

Thank you so much for taking the time to help me out.

I would be totally open to doing it in VBA. I'll just need help with that.

Thank you!