Table look up formula

[eit09]

I am creating a new template and cannot get the lookups & match functions to work together in a formula the way I need them to. I would like to create a formula that will take the values entered in Cells B4-B6 on sheet 1, search the table on sheet 2 and place the corresponding values on sheet one in cells B7 & B8.The table I entered on sheet 2 gives values for both "h" & "a₁" depending on the belt width, surcharge angle, and type of idler. Looking for help on how to go about having excel do the table searching. I have attached my Excel file.

 

[sdz]

For a start you should separate numbers and units. So B4=36, C4="in". The same goes for you lookup table. Use "Belt Width (in)" as your title and use numbers in the next row.

This will make your lookup functions work much better.

But I also have to ask if you can't calculate h and a1 directly without using a table? You could still include the table as a reference.

 

[MEM1]

Research Match and Index.

 

[electricpete]

xlsx. That's a few versions past my 2000.

=====================================
(2B)+(2B)' ?

 

[IDS]

As far as I know, you can't do lookups on merged cells, so you will need to set up a separate table with the top row number for each code, then you can do a lookup (or match) on that, and calculate the row number you want, and use that in an Index function.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MEM1]

Take a look at this table lookup sheet I have. I didn't write it, so I can't walk you through. It might give you an idea on how to procede.

 

[daviv]

Like MEM1 said, you need to use index and match. And add some conditions. See attachment. Hope this is what you need.

 

[daviv]

Here is the file.

 

[eit09]

daviv,

That formula you provided is awesome & works perfect! I do not follow it, but will be spending some time with that formula and the excel help menu. It looks like renaming the reference cells is big though