2 Dimensional Lookup by column & rows to return score grade

[loscherland]

I've created a table similar to a mileage chart to score different shop
performance figures

Eg.

*Score*, *Revenue, Sales per opening hr
R, SPOH*,
A, 1000, 5,
B, 800, 4,
C, 700, 3,
D, 600, 2,
E, 500, 1,
F, 400, 0,

There are 6 "score grades" A to F & 11 key performance indicators (each
with their own unique abreviated header - e.g. SPOH)

I want to return a score for each store for each indicator depending on
each stores result.

Eg.

Shop 1 - Revenue is 550 - Score for this would be E as the value is
greater then 500 & less then 600.
Shop 2 - Revenue is 800 - Score for this could be B as the value is
greater then/= to 800 & less then 1000.

Would it be the INDES & MATCH function i'd need to use for this?? I
can't figure out how i'd use that to return a grade A to F?

 

[Bryan Hessey]

The easy way would be to invert the table to

000 G 0
400 F 1
500 E 2
600 D 3
etc, and use

=vlookup(A1,D1:E8,2,True)

where A1 is your score, D1 to E8 is the location of your table, an
'true' uses the best found (but lower) value.

Hope this helps

 

[Guest]

Assuming your table is in cols A to L (cola =Grade, Cols B to L are your
KPIs) as shown below:

R SPOH
G 0 0
F 400 0
E 500 1
D 600 2
C 700 3
B 800 4
A 1000 5

If D10 contains the KPI value, and E10 the KPI heading e.g. SPOH, then try
in say F10:


=INDEX($A$2:$L$8,MATCH(D10,OFFSET(A1,1,MATCH(E10,B1:L1,0),7,1),1),1)

HTH