There are probably better ways to do this, but here's what I would do. I'm
assuming there are only 3 possible values in Col#1 (A, B, or C) and 273
possible values in Col#2(19.0/19.1/19.2 thru 30.0). I will call Col#1 the
"Class" and call Col#2 the "Score". The two formulas I use in my solution
are: CONCATENATE and VLOOKUP. I will use the first 6 columns in the
spreadsheet (Columns A, B, E will be hidden). Column C will be the "Class"
and Column D will be the "Score" and Column F will be the "Medal".
-----------------------------
Create A Lookup Table (Columns A/B)
-----------------------------
There is some upfront work to be done in Columns A & B (the lookup table),
but should only take about a minute or so if you use the autofill feature.
In Column A, put all the possible combinations of Class+Score (combined into
one 'word'). There should be 273 lines, plus 1 more (more on that in a
minute). In Column B, put the corresponding medal for each class+score
combo. These two columns will be hidden after get the spreadsheet finished.
The lookup table should look something like this:
Col. A Col. B
------- --------
A23.0 BRONZE
A23.1 BRONZE
thru
A29.9 GOLD
A30.0 GOLD
B19.0 BRONZE
B19.1 BRONZE
thru
B30.0 GOLD
C21.0 GOLD
C21.1 GOLD
thru
C30.0 GOLD
Z99.9 None (this is a dummy value that won't be used, but it's CRITICAL
to have)
There are two VERY IMPORTANT things to remember about the Lookup Table.
1) The list MUST be in ASCENDING order in Column A.
2) If you want your results to show “None†or “Nothing†or “U Suk†for
non-medal scores, then you need to put in something higher than C30.0 at the
end of the table (I used Z99.9). The VLOOKUP formula uses the last entry in
the lookup table when it doesn’t find a match.
----------------------------------------------
The Results Table (Columns C/D/E/F)
----------------------------------------------
As I mentioned before, Column C will have the “Classâ€, either A, B or C.
Column D will have a 3-digit numerical score down to the tenths, such as 19.1
or 28.0, etc. These are the only two columns that the user will input data
into. (IMPORTANT: Make sure Column D is formatted as Text, not a Number).
In Column E (which will eventually be hidden), use the CONCATENATE function.
This will combine Columns C & D into one ‘word’. The syntax is:
=CONCATENATE (C1,D1). The result will be “B19.1â€, if Cell C1 has “B†and
Column D1 has “19.1â€. Copy this formula all the way down to however many you
plan to do.
In Column F, use the VLOOKUP function. The syntax is =VLOOKUP. This will
look at the result in Cell E1 and look for that same value in the Lookup
Table (A1:B274) and return the value in the 2nd column of the lookup table
which is the corresponding medal. Use the function wizard; it really helps.
(IMPORTANT: Make sure you put the $ in the cell references as indicated above
so that they don’t change when you copy the formula all the way down). The
results table should look something like this:
Col. C Col. D Col. E Col. F
------- -------- --------- --------
B 19.1 B19.1 BRONZE
A 23.1 A23.1 BRONZE
A 11.4 A11.4 U Suk
-----------------------
The Final Product
-----------------------
After you hide you columns A/B/E, you will end up with the simple table you
were looking for with the medals filling in automatically. If you don’t want
‘U Suk’ to be seen all the way down your table when no data hasn’t even been
put in yet, just nest the VLOOKUP within an IF function. =IF(C4=â€â€,
“â€,VLOOKUP(E4,$A$1:$B$274,2)).
Also, you can do a drop down in Columns C & D so that the user is forced to
choose A/B/C and 19.0-30.0 so it doesn't screw up your formulas. Use Data
Validation to do this…
-Kaman
The IT dept people call before they call the real IT dept.
