# Formula to look up a value form a chart

#### SaraO

##### New Member
Hello,

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

B C D E F G H I
 ML Frame Chassis 1 Chassis 2 Chassis 3 Chassis 4 Chassis 5 Chassis 6 ML1 Frame 1 1-576 577-1152 1153-1728 ML2 Frame 2 1-576 577-1152 1153-1728 ML3 Frame 4 1-576 577-1152 1153-1728 ML4 Frame 3 1-576 577-864 ML5 Frame 1 1-576 577-864 ML6 Frame 3 1-576 577-864

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

<tbody>
</tbody>
 Fiber # ML Frame Chassis Tray Port

<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 # ML Frame Chassis Tray Port 782 ML3 Frame 4 Chassis 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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### duggie33

##### Board Regular
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

##### New Member
Hi Doug,

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

Thank you.

#### duggie33

##### Board Regular
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

##### Board Regular
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

##### New Member
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!

Replies
1
Views
275
Replies
4
Views
710
Replies
10
Views
1K
Replies
1
Views
291
Replies
3
Views
1K

Threads
1,195,993
Messages
6,012,745
Members
441,724
Latest member
Aalbid

### 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

### 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