Struggling to get a formula to pull results from a table

[brianaclark1967]

Hi all,

I'm new here so please forgive my amateur approach.

I have attached a simple table that I'm struggling with.

I want to enter a width & height, then I need the formula to check if the height is between 1 & 3, and the width is between 1 & 3 (result is 5), or width is between 4 & 6 (result is 10)

and so on, cross checking width against height to achieve the correct result.

The chart is very simple but you'll see what I'm trying to achieve.

I have three huge tables that I'm referencing at the moment with lookup, but they are enormous and makes the sheet so slow.

Thanks in anticipation for any help you can give.

Kind regards

Brian

 

[NARAYANK991]

Hi Brian ,

See if this is OK.

Narayan

 

[jeffreyweir]

In addition to Narayan's answer, you can also interpolate between the values if you need. Although the formula to do this is a real beast! See http://forum.chandoo.org/threads/formula-challenge-010-two-way-interpolation.11305/

 

[brianaclark1967]

Hi,

I may not have explained it too well.

I've uploaded another sheet with explanations on it, to hopefully clarify this.

The sheet shown is an example. i have 3 huge tables (table 1, table2, table 3) that is searched for results

Many thanks

Brian

 

[Haz]

Please upload an example with how your 3 tables looks like.
The file you uploaded is exactly the same as your the first one and the first formula still works on it.

 

[brianaclark1967]

Hi Haz,

Thank you so much for taking the time and trouble to help me.
The file is too large to upload as it is, so I've tried to upload a small test example so you can see what I'm trying to achieve.

Regards

Brian

 

[jeffreyweir]

Brian: See attached. I've converted any data to Excel Tables whereever I could. This is a habit you should get into. Also, I've unmerged your cells. Don't use merged cells if you can avoid it...they make things difficult in the long run.

I have three huge tables that I'm referencing at the moment with lookup, but they are enormous and makes the sheet so slow.

How many rows are your lookup tables? Hundreds? Thousands? Tens of Thousands?

Depending on their size, I can give you a much more complicated formula that is much, much faster.

I'll be covering this in more detail in my forthcoming book,
Excel for Superheroes & Evil Geniuses

 

[brianaclark1967]

Brian: See attached. I've converted any data to Excel Tables whereever I could. This is a habit you should get into. Also, I've unmerged your cells. Don't use merged cells if you can avoid it...they make things difficult in the long run.



How many rows are your lookup tables? Hundreds? Thousands? Tens of Thousands?

Depending on their size, I can give you a much more complicated formula that is much, much faster.

I'll be covering this in more detail in my forthcoming book,
Excel for Superheroes & Evil Geniuses
View attachment 13318

Hi Jeffrey,

Thank you for your time and effort helping me.

In table 1 there are 720 rows & 1200 columns (width 750mm - 1470mm, height 1750mm - 2950mm)
In table 2 there are 730 rows & 1200 columns (width 1100mm - 1830mm, height 1750mm - 2950mm)
In table 3 there are 1730 rows & 1200 columns (width 1100mm - 2830mm, height 1750mm - 2950mm)

I could mail you the file directly if you wish. It's way to large to upload on here unfortunately.

Kind regards

Brian

 

[NARAYANK991]

Hi Brian ,

Given your latest information that you want a lookup with a resolution of 1 mm , over a span of more than a metre , the correct way to go about this would be calculation rather than a lookup.

If you can formulate the rules for this calculation that will be easier to implement than a lookup.

Narayan

 

[jeffreyweir]

While a direct calculation would be good if possible, this volume of data won't pose any problems whatsoever, because we're only doing two lookups on it: one for the column, one for the row. This can be done lightning fast - particulary because we can probably use the 'Exact Match' form of lookup.

Sure, email a file to weir.jeff@gmail.com and I'll take a look. Note that I might get a chance to look at it tomorrow, but otherwise it will have to wait 4 days as I'm off on holiday.

 

[NARAYANK991]

Hi ,

The issue is not of the lookup ; the issue is over creating the table for the lookup ; if at a later stage the height or the width is increased by 100 mm. , imagine the additional data that has to be entered in all the tables ; it is a different matter that the tables have already been created , but in the long-term , if rules for calculation can be formulated , that would certainly be good.

Narayan

 

[Haz]

A formula that calculates the result would be preferred, yes.

Meanwhile, look at the uploaded file, even with huge lookup ranges the result can still be found lightning fast.

 

[jeffreyweir]

Hi Brian. Got your file, thanks. @NARAYANK991 is right: You can greatly condense the information down into a small table. For instance, just by giving the lower bounds of each pricing band, table3 can be reduced from 2 million cells to this:


If you create similar tables for all your data, and upload your sample file, someone can help you out.

 

[brianaclark1967]

Hi Brian. Got your file, thanks. @NARAYANK991 is right: You can greatly condense the information down into a small table. For instance, just by giving the lower bounds of each pricing band, table3 can be reduced from 2 million cells to this:
View attachment 13358

If you create similar tables for all your data, and upload your sample file, someone can help you out.

Hi Jeffrey,

I've attached the sheet with the condensed tables.

Hope this makes it easier.

Regards

Brian.