Index Match Formulas

[jonnie_rc]

Hi,

I have a problem with picking up rates. I have an average rate and i need to pull from a table the closest fixed rate to the average. I have a jobcode reference i need to use as well as a step. I have attached a sample of the problem. I however have got hundreds of lines of this with many different rates and codes etc.



I ahve tried to use INDEX(MATCH.......formula but i cannot seem to get it to work.

Any help would be welcomed.

Thanks in advance,
john

[xor]

Please explain how you determine the 27,8244999932495 in Summary!B3

[ArviLaanemets]

To have MATCH to return the position of nearest value, the column where you search for match (99 in your example) must be sorted in right order - otherwise when you are searching match p.e. for 5 in values list 1, 3, 6, 4 in growing order, the result will be 2 (the position of number 3) instead of 4 - because at position 3 is number 6 which is greater than 5. And value list 1, 3, 2, 4, 6 for same example will give also 2 as result, as at position 3 is number smaller then previous one.

[xor]

Is it something like the attached?

[jonnie_rc]

Originally Posted by xor

Is it something like the attached?

Thanks for the formula. It works alright for the jobcode. What i wa shoping to achieve was get the nearest code in the range of jobcodes QARVP 0-3.

I am not sure if it is possible.

I will manage with what I have.

Thanks for your time. Much appreciated.

[xor]

I am not sure what is missing. Can't you be a bit more helpful giving some examples of expected results?

[jonnie_rc]

Hi don't worry about it, it's all good.

Basically i have a group of paycodes based on different levels of experience, hence the 0,1,2,& 3. My payroll tool and HR tool however do not talk to eachother so Payroll has them all down as QARXX0 and not any 1,2 or 3. The average rate in my file however is based on the 1,2,3.

So what i was trying to do was based on the 0 code i have in payroll, find the closest rate in the sample rate i gave to the correct code. I have hundreds of paycodes like the sample provided.

Not sure if that makes sense. But like i said, dont worry. Its all good.

Thanks again,
Jon

[xor]

I actually made a serious attempt in creating the formula so it is a bit frustrating for me if it is not what you want. But it is difficult for me to understand what you write as you do not give examples of expected results (it may be due to the fact that English is not my mother tongue).

I attach a new version of the file where I have added QARVP1, QARVP2 and QARVP3 to Summary!C4:C6 and corresponding formulas in Summary!E4:E6

[xor]

I don't know if you at all are interested, but if you are, here is a corrected file. In the previous file I had forgotten som absolute references.

[jonnie_rc]

Thanks.

Yes this is useful.

An example of what exactly i need is of say the rate in B3 was 30.445then i would like the formula to give me the rate at QARVP2 step 10 of 30.42. That way I would knwo to use that jobcode when entering budget.

Kind regards,
Jon

[xor]

Just one last thing.
The formulas in Summary!E3:E6 are array formulas so please be aware that when entering such a formula you must hold down Ctrl and Shift before pressing Enter (instead of just pressing Enter). If you do it correctly Excel will put brackets around the formula {}. Don't try to enter these brackets manually. When you have entered one array formula correctly you can copy it to other cells as you normally do.