See if you can help me solve this one!!!

:Bismarck

I don't get it: Given 2500 as the amount and 42% as the percent, what does 500 mean? In other words, how is that number composed, given the amount and the percent?

Aladin

The number was simply symbolic. My point is that being given a column heading and a row heading, I need to find where those two meet. That one cell must then be returned to me.

Well, in that case, care to provide a snippet of your real data if possible? Here is how: Activate an empty cell, enter =, select the relevant but a small portion of data, then hit control+shift+enter at the same time. You'll see at the formula bar a range put between 2 braces {}. Now: select the range in the formula and hit F9. Copy the resulting array and paste it into your post.

Aladin

{" Mark-up","< $960,000","$960,001 - $1,199,999","$1,200,000 <";"4 % or Under",0.0008,0.001,0.0012;"4.01% - 6%",0.0009,0.0012,0.0014;"Over 6.01%",0.001,0.0014,0.0017}

Let me know if this helps, or if you need something else.

Thanks,
Bis

Regards,

Try this

Assuming B2:D4 is where the numbers are, and B7 is where you put the Percent and B6 is where you put the Amount

=INDEX(B2:D4,IF(B7<40,1,IF(B7<50,2,IF(B7<=60,3))),IF(B6<2000,1,IF(B6<6000,2,IF(B6<=8000,3))))

This'll work for a small range but for a bigger data set you might want to use a LOOKUP function, let me know if this is the case.

VLOOKUP

Try this. Remember, I didn't include for the headings PERCENTS and AMOUNTS so, you may have to change the range in the formula.
I assume the A2 is "0 - 40%" and B2 is "40-50%" and C2 is "50-60%".
Again, B7 is where you put the Percent [e.g 23] and B6 is where you put the Amount [e.g. 1500].

=VLOOKUP(IF(B7<40,A2,IF(B7<50,A3,IF(B7<=60,A4))),A2:D4,IF(B6<2001,2,IF(B6<6001,3,IF(B6<=8000,4))))

====================

Bis

I propose a small reorganization of your table of data as follows:

{0,0,960000,1200000;0,0.0008,0.001,0.0012;0.04,0.0009,0.0012,0.0014;0.06,0.001,0.0014,0.0017}

where he top row consists of

B1 $0.0
C1 $960,000
D1 $1,200,000

and the first column

A2 0%
A3 4%
A4 6%

I assumed that this table of data occupies thus the range A1:D4 (A1 is empty).

Select the range B1:D1 and name it AMOUNTS via the Name Box.
Select the range A2:D4 and name it TOTALS via the Name Box.

In A9 enter: ENTER AMOUNT:
Name the cell B9 "Amount" via the Name Box.

In A10 enter: ENTER PERCENT:
Name B10 "Percent" and format this cell by means of the %-icon.

In A11 enter: TOTAL EQUALS:
In B11 enter: =VLOOKUP(Percent,TOTALS,LOOKUP(Amount,AMOUNTS,1)+1,1)

Note that if you didn't or couldn't name the ranges and cells as I indicated, then use the actual references in the formula of B11.

Note also that LOOKUP in the formula determines the column of AMOUNTS where to look for a total given a percent.

Aladin

Correction...

==================== Bis I propose a small reorganization of your table of data as follows: {0,0,960000,1200000;0,0.0008,0.001,0.0012;0.04,0.0009,0.0012,0.0014;0.06,0.001,0.0014,0.0017} where he top row consists of B1 $0.0 C1 $960,000 D1 $1,200,000 and the first column A2 0% A3 4% A4 6% I assumed that this table of data occupies thus the range A1:D4 (A1 is empty). Select the range B1:D1 and name it AMOUNTS via the Name Box. Select the range A2:D4 and name it TOTALS via the Name Box. In A9 enter: ENTER AMOUNT: Name the cell B9 "Amount" via the Name Box. In A10 enter: ENTER PERCENT: Name B10 "Percent" and format this cell by means of the %-icon. In A11 enter: TOTAL EQUALS: In B11 enter: =VLOOKUP(Percent,TOTALS,LOOKUP(Amount,AMOUNTS,1)+1,1) Note that if you didn't or couldn't name the ranges and cells as I indicated, then use the actual references in the formula of B11. Note also that LOOKUP in the formula determines the column of AMOUNTS where to look for a total given a percent. Aladin

Bis:
Replace LOOKUP with MATCH.
I plugged in the wrong function in that VLOOKUP-formula.

Aladin

Re: Correction...

Thanks for everything Aladin. It worked GREAT!

Best Regards,
Bis