Lookup value based on specified number sequences

Hello All,

I need to come up with a formula that will lookup a value in a table based upon a specified number sequence in another table and then add that value to another number/price that corresponds with that specified number sequence.

In the attached worksheet you will see that there are two groups of tables (A & B). I want to take the prices in group A in columns B:E and then add the values at the top of the page in columns G:J based upon specific numbers to the right of the decimal place in column A under "Rate".

So in the example at the bottom you will see that the first rate in column A is 5.125. Since the rate ends in .125 then it should be using the value of 2.57 from J2. So it should take the price in B27 (108.328) and add 2.57 to give you 110.898 in M27.

My problem is that the rate sequence wont always be the same and neither will the values in columns G:J. The only constants are that the rates will end in 1/8ths and the values will be based upon which 1/8ths they end in. For instance; In the example the rate range goes from 5.125 down to 3.75 but sometimes it may be 5.625 down to 4.25.

Thanks for your help!

Formula 5-8-14.xlsx

I tried to do it without changing the data and came up with;
=B9+INDEX($G$3:$J$3,SUM(($A9=ROUNDDOWN($A9,0)+LEFT($G$2:$J$2,SEARCH("&",$G$2:$J$2,1)-1))*COLUMN($A$1:$D$4))+SUM(($A9=ROUNDDOWN($A9,0)+RIGHT($G$2:$J$2,LEN($G$2:$J$2)-SEARCH("&",$G$2:$J$2,1)-1))*COLUMN($A$1:$D$4)))
as an array formula (confirm with ctrl+shift+enter)in M9 and copy across/down. It works for your example but will assume that your rates headings always have a similar form.

Originally Posted by ragulduy

I tried to do it without changing the data and came up with;
=B9+INDEX($G$3:$J$3,SUM(($A9=ROUNDDOWN($A9,0)+LEFT($G$2:$J$2,SEARCH("&",$G$2:$J$2,1)-1))*COLUMN($A$1:$D$4))+SUM(($A9=ROUNDDOWN($A9,0)+RIGHT($G$2:$J$2,LEN($G$2:$J$2)-SEARCH("&",$G$2:$J$2,1)-1))*COLUMN($A$1:$D$4)))
as an array formula (confirm with ctrl+shift+enter)in M9 and copy across/down. It works for your example but will assume that your rates headings always have a similar form.

WOW ragulduy!!!

Care to explain your formula so that I can learn too!!!

Why with SUM? As well, what's the *COLUMN($A$1:$D$4) formula does?

Originally Posted by ragulduy

I tried to do it without changing the data and came up with;
=B9+INDEX($G$3:$J$3,SUM(($A9=ROUNDDOWN($A9,0)+LEFT($G$2:$J$2,SEARCH("&",$G$2:$J$2,1)-1))*COLUMN($A$1:$D$4))+SUM(($A9=ROUNDDOWN($A9,0)+RIGHT($G$2:$J$2,LEN($G$2:$J$2)-SEARCH("&",$G$2:$J$2,1)-1))*COLUMN($A$1:$D$4)))
as an array formula (confirm with ctrl+shift+enter)in M9 and copy across/down. It works for your example but will assume that your rates headings always have a similar form.

I tried this and it seemed to work. Can you tell me why this piece is in the formula? COLUMN($A$1:$D$4)

I'm just trying to learn.

Originally Posted by etaf

i have rearranged the table you have in G:J - so that each value is in a cell
and then used index match to lookup

this is just for 15days - and probably can be set to you can simply copy across

=ROUND(INDEX($B$9:$B$20,MATCH($L9,$A$9:$A$20,0))+HLOOKUP(MOD($L9,1),$L$2:$S$3,2,0),3)

at the moment to do the next column you would need to change the
=ROUND(INDEX($B$9:$B$20,MATCH($L9,$A$9:$A$20,0))+HLOOKUP(MOD($L9,1),$L$2:$S$3,2,0),3)
to
=ROUND(INDEX($C$9:$C$20,MATCH($L9,$A$9:$A$20,0))+HLOOKUP(MOD($L9,1),$L$2:$S$3,2,0),3)

see if that works for you , then we can change as required

so in the G:J you can put any decimal part you need

I tried using this and changing the 5.125 rate in A9 to 5.375 and it spit out an error?

Hi, I would suggest using the formula auditing tool to step through the formula to see what each bit does but to try and explain the logic:
SEARCH("&",$G$2:$J$2,1)
will find the position of the & in each of the cells in G2:J2 and return the results as an array of numbers for each cell
LEFT($G$2:$J$2,SEARCH("&",$G$2:$J$2,1)-1)
will then return the left hand number in each of the cells as an array
A9=ROUNDDOWN($A9,0)+LEFT($G$2:$J$2,SEARCH("&",$G$2:$J$2,1)-1))
will compare the value from the table to the values in the array and return true/false dependant on if they are the same.
Column($A$1:$D$4) returns the column numbers of A1:D4 as an array (i.e. {1,2,3,4} which are then multipled against the true false results to return the 1 if the G2 is the correct rate, 2 if H2 is the correct rate, 3 if I2 is the correct rate and 4 if J2 is the correct rate.

I then do the same thing but using the right function to get the second rate in each cell and use the sum() function to get the total of all the different numbers, so if the first number of H2 is the correct rate I would get SUM({0+0,2+0,0+0,0+0}) which =SUM({0,2,0,0}) which = 2, and I then use this with the index function to pick out the correct number to add from the G3:J3 range.

Thanks for the explanation ragulduy!

You mention of formula auditing tool, is that an add-in?

no, it's part of the "normal" excel.

Originally Posted by etaf

i'm using L9 to look up and not A9
=ROUND(INDEX($B$9:$B$20,MATCH($L9,$A$9:$A$20,0))+HLOOKUP(MOD($L9,1),$L$2:$S$3,2,0),3)

so if in L9 you put =A9 it will work

or you can change L9 to A9 in the formula

Got it. Thanks!

Originally Posted by ragulduy

I tried to do it without changing the data and came up with;
=B9+INDEX($G$3:$J$3,SUM(($A9=ROUNDDOWN($A9,0)+LEFT($G$2:$J$2,SEARCH("&",$G$2:$J$2,1)-1))*COLUMN($A$1:$D$4))+SUM(($A9=ROUNDDOWN($A9,0)+RIGHT($G$2:$J$2,LEN($G$2:$J$2)-SEARCH("&",$G$2:$J$2,1)-1))*COLUMN($A$1:$D$4)))
as an array formula (confirm with ctrl+shift+enter)in M9 and copy across/down. It works for your example but will assume that your rates headings always have a similar form.

Thank you so much ragulduy! Its amazing what you can do when you know what you are doing.