Hello Duke,
I gave it another try this morning. This is what I placed in the cell,
=INDEX('Lookup Table'!C2:C17,MATCH(H2&L2,'Lookup Table'!A2:A17&'Lookup
Table'!B2:B17,0))
I committed it as an array. I get the #VALUE error now. The help seems to be
telling me that I have a constant in the array. Any ideas what this could
mean?
"Duke Carey" wrote:
Where Bob's formula hard-codes a 20 and an A, you want to substitute a
reference to the cells that contain your Collateral code and Tier
Thus, if your spreadsheet has the target Collateral Code and Tier on cells
A2 & B2, and you want the Rate in C2, you'll put the formula in cell C2. Of
course modify the C4:C13 and A4:A13&B4:B13 addresses to match your range of
lookup data.
=INDEX(C4:C13,MATCH(A2&B2,A4:A13&B4:B13,0))
Commit the formula by pressing Shift-Ctrl-Enter
Duke
"Russell Hampton" wrote in
message ...
I hate to seem like a complete dolt here but I am just not getting this. I
tried what you suggested along with several variations. I put the
information
that I have in this email. It is the blank that I am trying to get for
this
example of new rate. I think that I just have too many options. I
understand
if you want to give up on me. ha ha Russell
COLL TIER NEW RATE
22 B
Collateral Code Tier Rate
25 A 18.00%
25 B 18.00%
25 C 18.00%
25 D 18.00%
22 A 13.50%
22 B 13.50%
22 C 17.50%
22 D 18.00%
26 A 12.50%
26 B 12.50%
26 C 15.50%
26 D 18.00%
27 A 9.50%
27 B 9.50%
27 C 13.50%
27 D 16.50%
"Bob Phillips" wrote:
Russell,
As there will be multiple A for rates, I would not hold it all in one
table
but have two tables and use the results of one lookup to do a lookup into
the second.
However, if you want to continue as you were, assuming the number is in
A,
the letter is in B, and the rate is in C1, use
=INDEX(C1:C10,MATCH(20&"A",A1:A10&B1:B10,0))
which is an array formula, so commit it with Ctrl-Shift-Enter
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Russell Hampton" wrote in
message ...
Thanks Bob. I think that I have it working. Here is a followup
question.
I have the column returning the A or B. Now that information along with
a
third column needs to be used to give me a rate. So A and 21 is 9.5 but
A
and
22 is 10.5. The VLOOKUP doesn't seem to like the three dimensions that
I
think I will need in my table. Is there another type of formula that
will
work. Sometimes I think that it would be easier for MS to give us IF,
THEN
and ELSE statements. Thanks again, Russell
"Bob Phillips" wrote:
=VLOOKUP(A1,{720,"A";600,"B"},2,FALSE)
just extend that range or put it into a worksheet range and use that.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Russell Hampton" wrote in
message ...
I am trying to create a column that will convert a number score
into a
letter
score based on a range. For instance, if the cell has 720 in it
then I
want
an A to print in another cell. If the number is 600 then a C should
print.
I
can get it to print using an IF statement so long as there is only
one
option. Any ideas for multiple options? I tried VLOOKUP but that
doesn't
work.
|