Look up value in linear list based on two criteria

[Scrannigan]

I won't waffle on to much about the back story but the crux of the issue is I have a a selection of products

These products have a number of performance related parameters

There are ten products

Each product has a capacity range at at a particular capacity there is an associated loss, the capacities and losses are totally different for each product

I want to be able to select a product

Input a capacity which would then populate a field with the closest loss relavant to that product only


So for example

Product 1
Capacity 1
Loss 2
Capacity 2
Loss 4
Capacity 3
Loss 6
Capacity 4
Loss 8
Capacity 5
Loss 10

Product 2
Capacity 2
Loss 3
Capacity 3
Loss 6
Capacity 4
Loss 9
Capacity 5
Loss 12
Capacity 6
Loss 15

Product 3
Capacity 3
Loss 4
Capacity 4
Loss 8
Capacity 5
Loss 12
Capacity 6
Loss 16
Capacity 7
Loss 18

Etc

I need to be able to select Product 1, input capacity 2 and get loss 4, but if I change to product 2 the loss would ultimately change.

There's I also other simpler data associated to each product that I can auto populate with vlook

But this one has me stumped

I hope that makes sense

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[Scrannigan]

Many thanks Glenn

A simple sample of what I'm trying to achieve is (hopefully) now attached

[Glenn Kennedy]

It's not labelled!! Which is the raw data? Where are the inpts? Where are the desired outputs?

[Scrannigan]

Apologies Glenn.

I think I've now added annotations that should identify the relevant components

[6StringJazzer]

Thanks for providing your sample file. It's totally different that what you showed in your first post.

Try this formula in B17:

Please Login or Register  to view this content.

[Gregb11]

This will also give you the next lowest loss if the capacity doesn't equal the list:

=INDEX(INDEX(A23:H27,,MATCH($B$11,$A$21:$H$21,0)+1),MATCH($B$16,INDEX(A23:H27,,MATCH($B$11,$A$21:$H$21,0)),1))