Formula to look up a value form a chart

SaraO

New Member
Joined
Feb 4, 2019
Messages
21
Hello,

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

B C D E F G H I
MLFrameChassis 1Chassis 2Chassis 3Chassis 4Chassis 5Chassis 6
ML1Frame 11-576577-11521153-1728
ML2Frame 21-576577-11521153-1728
ML3Frame 41-576577-11521153-1728
ML4Frame 31-576577-864
ML5Frame 11-576577-864
ML6Frame 31-576577-864

<tbody>
</tbody>
Another chart that looks like this... (Chart 2)

<tbody>
</tbody>
Fiber #MLFrameChassisTrayPort

<tbody>
</tbody>

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:

Fiber #MLFrameChassisTrayPort
782ML3Frame 4Chassis 2

<tbody>
</tbody>

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

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
Hi Doug,

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

Thank you.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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