Lookup Values Between 2 columns
I need to look up a value that uses 2 different lookups. I need to look up the size in column J and see where it falls between the min & max in columns N & O using only rows 2-9 and only if the prod hours in column G are less than 40 and then give me the value in column P.
Then I need to look up up the Prod Hours in column G and if it's greater than 40 I need to see where it falls between the min & max in columns N & O but only using rows 10, 11, & 12 and then give me the value in column P.
The highlighted cells in the example file are what the value's should be based on the above descriptions.
Sample-File-R1.xls
Then I need to look up up the Prod Hours in column G and if it's greater than 40 I need to see where it falls between the min & max in columns N & O but only using rows 10, 11, & 12 and then give me the value in column P.
The highlighted cells in the example file are what the value's should be based on the above descriptions.
Sample-File-R1.xls
Insert in cell K2 and then copy down:
=IF(G2<=40,VLOOKUP(J2,$N$2
You need the TRUE conditions to ensure that you are finding an approximate match in your sorted list.
Two more things:
1) you don't *need* the values in column O, as your list is sorted already
2) I recommend that you move your lookup list to another location so as not to interfere with your data range in columns A-L
=IF(G2<=40,VLOOKUP(J2,$N$2
You need the TRUE conditions to ensure that you are finding an approximate match in your sorted list.
Two more things:
1) you don't *need* the values in column O, as your list is sorted already
2) I recommend that you move your lookup list to another location so as not to interfere with your data range in columns A-L
ASKER
That doesn't work. If the prod hours are >40 but does not fit in any of the ranges in cells N10:P12 then it needs to look up in cells N2:P9 to find the right work center.
You did not specify that in your original post. Additionally, the formula provided by me produces the same results that were manually shown in column K. Is that not correct?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much! It works perfectly!!!
Glad to help, thanks for the grade. One warning, if your size is greater than 4, the last line will still be returned, you'll have to modify the formula to return an error.
ASKER
Thanks Thomas. We'll never have a size greater than 4 so we should never run into that problem. But thanks for pointing it out.
Thomas