index problem

aladinalamp

New Member
Joined
Sep 15, 2006
Messages
31
Please help, I am trying to fill in the grade by cross referencing the sales per hour and the matrix per hour. The point that these cross in the chart should return a pay grade letter.
Book1.xls
ABCDEFGHIJKLM
1
2MatrixPerHourSalesPerHourGrade
356520125+BBCDEF
4100-125BBCDEE
575-100BBCDDD
655-75ABCCCC
740-55ABBCCC
825-40AABBBB
9275-400400-500500-600600-700700-850850+
10
11
12
13
14
15
16
17
Sheet1


many thanks in advance
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Change your column G values to the lower of the two, and change Row 9 to the higher of the two, make 850+, 100000 or higher. Sort the table via Col G so 25 is atr the top and 125 is at the bottom.

Does this give the correct answer

=VLOOKUP(B3,$G$3:$M$8,MATCH(C3,$H$9:$M$9,1)+2)

I get C for your example.
 

aladinalamp

New Member
Joined
Sep 15, 2006
Messages
31
Not sure that works

This is the data that I am working with the letters in the blue are the results I need from the data in columns H and I. I have made the amends that you suggested but they dont return what I am after. I know you are on the right lines but just cant figure it out. The columns H and I are in Currency format to .0 places.
QUESTION.xls
BCDEFGHIJKLMNOPQRSTUV
2SalesHoursTPCEssentialsTotalMatrixMatrixPerHourSalesPerHourGradePerformanceContrcat
3Colleague123,683394271,0281,45537.31607B  GO125BBCDEF
4Colleague213,748359539849314.09393  GO100BBCDEE
5Colleague315,284399171,1982,11554.23392A  GO75BBCDDD
6Colleague46,1021823027350327.94339A  GO55ABCCCC
7Colleague512,861183731,2281,60188.94715D  GO40ABBCCC
8Colleague614,366395507341,28432.92368A  GO25AABBBB
9Colleague7#REF!#REF!#DIV/0!  GO400500600700850100000
Week 2


Thanks in advance
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Expand and re-arrange the lookup table you have:
Book1
ABCDEFGHIJKL
10400500600700850100000
20       45608B
325 AABBBB0700 
440 ABBCCC160850E
555 ABCCCC
675 BBCDDD
7100 BBCDEE
8125 BBCDEF
Sheet2


B2:

=""

Copy this down to B8 and across to H8.

The formula in L2 shows how to use the new table:

=INDEX($B$2:$H$8,MATCH(J2,$A$2:$A$8,1),MATCH(K2,$B$1:$H$1,1))
 

Forum statistics

Threads
1,141,587
Messages
5,707,247
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top