finding values in a table using 2 criteria (changed)

[Willem]

A B rating 60 65 70 75 80 85 90 95 100
74 E A 0 0 0 0 0 0 0 0 0
84 C B 0 0 0 0 0 0 0 0 0
86 D C 5 4 4 3 3 2 1 0 0
70 D D 8 7 5 4 4 3 2 1 0
72 E E 9 8 7 6 5 4 3 2 1
88 C
52 E

I am looking for help on the following problem.
In Column A I have values from 65 to 99 (random) representing a percentage.
In column B I have ratings from A to E.
Also I have a table with (max) relative positions in the columns(65, 70, 75,
80, 85, 90, 95, 100) and a percentage depending on these ratings on rows, so
this is a matrix of 8 columns by 5 rows.
The actual relative position is shown in cell A3, the rating in cell B3. Now
I want to compare the value in A3 with the max. relative position in the
table, lookup the value that corresponds with the rating in B3, and place it
in cell C3 and so on.
I have tried combining HLOOKUP and VLOOKUP, but that seems to be the wrong
solution.
Does anybody here have another bright idea?
I can't include a copy of the sheet, but I hope the description helps.
Looking forward to your solutions.

Willem.

 

[Niek Otten]

Hi Willem,

First, include a column with 0 in the heading, so 52 can be found as well.
You'll probably have to shift all the values one column, so it looks like this:

rating
0
60
65
70
75
80
85
90
95
100

A
0
0
0
0
0
0
0
0
0
#N/A

B
0
0
0
0
0
0
0
0
0
#N/A

C
5
4
4
3
3
2
1
0
0
#N/A

D
8
7
5
4
4
3
2
1
0
#N/A

E
9
8
7
6
5
4
3
2
1
#N/A



With your table in C1:M6 (including the heading):

=HLOOKUP(A3,$D$1:$M$6,MATCH(B3,$C$2:$C$6,0)+1)


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|A B rating 60 65 70 75 80 85 90 95 100
| 74 E A 0 0 0 0 0 0 0 0 0
| 84 C B 0 0 0 0 0 0 0 0 0
| 86 D C 5 4 4 3 3 2 1 0 0
| 70 D D 8 7 5 4 4 3 2 1 0
| 72 E E 9 8 7 6 5 4 3 2 1
| 88 C
| 52 E
|
| I am looking for help on the following problem.
| In Column A I have values from 65 to 99 (random) representing a percentage.
| In column B I have ratings from A to E.
| Also I have a table with (max) relative positions in the columns(65, 70, 75,
| 80, 85, 90, 95, 100) and a percentage depending on these ratings on rows, so
| this is a matrix of 8 columns by 5 rows.
| The actual relative position is shown in cell A3, the rating in cell B3. Now
| I want to compare the value in A3 with the max. relative position in the
| table, lookup the value that corresponds with the rating in B3, and place it
| in cell C3 and so on.
| I have tried combining HLOOKUP and VLOOKUP, but that seems to be the wrong
| solution.
| Does anybody here have another bright idea?
| I can't include a copy of the sheet, but I hope the description helps.
| Looking forward to your solutions.
|
| Willem.
|
|
|

 

[Niek Otten]

I should have known; the table format is screwed up.

Transpose them to a horizontal layout

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Willem,
|
| First, include a column with 0 in the heading, so 52 can be found as well.
| You'll probably have to shift all the values one column, so it looks like this:
|
| rating
| 0
| 60
| 65
| 70
| 75
| 80
| 85
| 90
| 95
| 100
|
| A
| 0
| 0
| 0
| 0
| 0
| 0
| 0
| 0
| 0
| #N/A
|
| B
| 0
| 0
| 0
| 0
| 0
| 0
| 0
| 0
| 0
| #N/A
|
| C
| 5
| 4
| 4
| 3
| 3
| 2
| 1
| 0
| 0
| #N/A
|
| D
| 8
| 7
| 5
| 4
| 4
| 3
| 2
| 1
| 0
| #N/A
|
| E
| 9
| 8
| 7
| 6
| 5
| 4
| 3
| 2
| 1
| #N/A
|
|
|
| With your table in C1:M6 (including the heading):
|
| =HLOOKUP(A3,$D$1:$M$6,MATCH(B3,$C$2:$C$6,0)+1)
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
||A B rating 60 65 70 75 80 85 90 95 100
|| 74 E A 0 0 0 0 0 0 0 0 0
|| 84 C B 0 0 0 0 0 0 0 0 0
|| 86 D C 5 4 4 3 3 2 1 0 0
|| 70 D D 8 7 5 4 4 3 2 1 0
|| 72 E E 9 8 7 6 5 4 3 2 1
|| 88 C
|| 52 E
||
|| I am looking for help on the following problem.
|| In Column A I have values from 65 to 99 (random) representing a percentage.
|| In column B I have ratings from A to E.
|| Also I have a table with (max) relative positions in the columns(65, 70, 75,
|| 80, 85, 90, 95, 100) and a percentage depending on these ratings on rows, so
|| this is a matrix of 8 columns by 5 rows.
|| The actual relative position is shown in cell A3, the rating in cell B3. Now
|| I want to compare the value in A3 with the max. relative position in the
|| table, lookup the value that corresponds with the rating in B3, and place it
|| in cell C3 and so on.
|| I have tried combining HLOOKUP and VLOOKUP, but that seems to be the wrong
|| solution.
|| Does anybody here have another bright idea?
|| I can't include a copy of the sheet, but I hope the description helps.
|| Looking forward to your solutions.
||
|| Willem.
||
||
||
|
|

 

[Willem]

hmm thanks?
Is there a reason why I can't find the data in a simple matrix? It looks
like an extra step that should not be necessary.
doesn't look very sophisticated, but I will try anyway.
Any other solutions are welcome!

Willem.