Lookup Table formula

Hello.

The attached Sample Reference Speed table Sample - Speed Reference.xlsx reflects the lookup table and the Daily Activity sheet I need help with.

I need assistance with a formula to be placed in Column I (Technical Speed), which will supply the standard speed based off of what the User keys for a Product No.

Can someone please assist me with this.

Thanks a bunch. Deanna

Please Login or Register  to view this content.

Type the product number in cell B17 and it will return the standard speed. Is that what you are looking for? You can change the cell reference to suit.

Hello Hurricanefly,

Product No look up and returning speed is great. However, independant from returning speed, I also need the cell below (ie. 2800 and below speed it has a number like 8, which is the Cycles/Iterations that I need to also automatically return from a lookup based on Product No entered by User. I need to have the Speed Lookup and Cycles/Iterations Lookup return automatically based on what the User keys for Product No.

It does not matter if they function independantly or in sync when User keys Product No. It would be nice if when the User keys Product No. that "Technical Speed" automacially pops up with the Standard Speed and Standard Cycles/Iteration - a formula I can put in both cells.

Product number in cell B17 (Change to which one you like) This in cell C17

Please Login or Register  to view this content.

and this in cell D17

Please Login or Register  to view this content.

This will return the standard speed and cycles based on product number entered. Now you want Technical Speed too? based on standard speed?

Im not too sure where all the Technical Speed data is, but based on product number 850 you can place this formula in cell E17

Please Login or Register  to view this content.

as far as I can see this will return all the criteria from product number 850. I have tried it with product number 12000 but I cant see where I am getting the Technical speed for that. Is this any help?

Hello Huricanefly,

I placed =VLOOKUP (B17, tblStdSpeed!A2:C34, 2, FALSE) = Technical Speed

I placed = VLOOKUP (B17, tblStdSpeed!A2:C34, 3, FALSE) = Cycles/Iterations

It worked great! Whenever I keyed the Product No., both the Technical Speed and Cycles/Iterations automatically populated the 'Strokes/Hour' area. Thank you.

However, I am not quit sure what =VLOOKUP(C17, H3:I3, 2, FALSE) is used for. Can you explain?

Hi, it was my mistake sorry. I'm glad to be of help, so you'r problem is now solved?

Hello Hurricanefly.

The above worked great, except for #N/A error. Do you have any suggestions for modifying these formulas for error trapping? I need to avoid #N/A; #DIV!; and any other error they may occur.

Thank you.

Hi, what cells do you have the (Vlookup in) and which cells have the formulas in? or have you added a formula to the (Vlookup)?

Hello Hurricanefly,

I troubleshot last night and came up with the following:

1) =IF(D8="",0,VLOOKUP(D8,tblLine1StdSpeed!A2:C34,2,FALSE))

and

2) =IF(D8="",0,VLOOKUP(D8,tblLine1StdSpeed!A2:C34,3,FALSE))

This is the best I came up with that works. It prevents #N/A from popping up when there is nothing in the Product No. input cell. If you know a better error trapping formula, please advise.

Thanks a bunch for your help though. Deanna

You can try this if you want the Vlookup cells to be blank when there is no product number.

Please Login or Register  to view this content.

In the speed cell.

Please Login or Register  to view this content.

In the cycles cell.