Index Match with several criteria

**Ska** · 09-16-2010, 11:30 AM

Hi All
Any Help much appreciated.

I'm trying to use Index match formula to return hourly rate values as shown in the attched sheet.
I.e. there are two service providers who supplies servcies for a set amount depending on the number supplied, naturaly the rates differ from provider to provider.
I want to be able to return the correct hourly rate matching the the Provider and rate applicable depending on the number of total hours. If this makes any sense. I thought I had it cracked but when I entered the 3rd If Index Match statement it doesn't return the right hourly rate for either of the two providers. Is there another way I can do this?
Once again any help would be much appreciated.
Thanks

**arthurbr** · 09-16-2010, 11:44 AM

Hi,
does the attached help? ( I added a row of values, you can put them anywhere you like adapting the formula)

**sailepaty** · 09-16-2010, 12:20 PM

Try this,

=INDEX($G$4:$K$5,MATCH(B2,$F$4:$F$5,0),MATCH(B3,{0,201,500,1000,2000},1))

You can type the hours range on cells G1 thru K1 and the formula will look like this

=INDEX($G$4:$K$5,MATCH(B2,$F$4:$F$5,0),MATCH(B3,$G$1:$K$1,1))

Regards

**Ska** · 09-17-2010, 06:07 AM

Originally Posted by arthurbr

Hi,
does the attached help? ( I added a row of values, you can put them anywhere you like adapting the formula)

Sorry jumped the gun, I have a further predicament – the total number of hours is actually in the format of [hh]:mm and not a general number so does not return the correct hourly rate (swiching is back to general gives a decimal figure all of which are way under 200hours). Changing the format to [hh]:mm for the hourly ranges in G6:K6 does not help either. Is there a go around for this?
Thanks

**Ska** · 09-17-2010, 04:50 AM

Many Thanks Guys they all work!
PS how do you set the thread to solved?
Thanks

**teylyn** · 09-17-2010, 04:55 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save