×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Table look up formula

Table look up formula

Table look up formula

(OP)
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" & "a1" 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.

RE: Table look up formula

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.  

RE: Table look up formula

Research Match and Index.

RE: Table look up formula

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

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

RE: Table look up formula

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/
 

RE: Table look up formula

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

RE: Table look up formula

(OP)
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
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources