IF function along with variables

**vs4hire** · 07-07-2007, 09:19 AM

Here's an example of what I'm trying to accomplish with a formula:

A B C D E
1 33789 4 26001 28000 1
2 23000 ERROR 28001 30000 2
3 27999 1 30001 32000 3
4 32001 34000 4
5 34001 36000 5

Columns C, D, & E are fixed values. User would input an amount into A1 and then B1 would populate based on the value compared to columns C, D, & E.

What I need to say, but just not sure how is:
IF A1 > C1 BUT < D1 THEN B1=E1 ELSE IF A1 > C2 BUT < D2 THEN B1=E2 ELSE IF A1 > C3 BUT < D3 THEN B1=E3 etc etc etc

I have about 27 rows (of "ranges") that I need to compare that number to - so I figured there has to be an easier way than having a formula that's a mile long...any suggestions??

Thank you SO much!
Michelle

**nattasiray** · 07-07-2007, 11:33 AM

Originally Posted by vs4hire

Here's an example of what I'm trying to accomplish with a formula:

A B C D E
1 33789 4 26001 28000 1
2 23000 ERROR 28001 30000 2
3 27999 1 30001 32000 3
4 32001 34000 4
5 34001 36000 5

Columns C, D, & E are fixed values. User would input an amount into A1 and then B1 would populate based on the value compared to columns C, D, & E.

What I need to say, but just not sure how is:
IF A1 > C1 BUT ____< D1 THEN
B1=E1
ELSEIF A1 > C2 BUT ____< D2 THEN
B1=E2
ELSEIF
A1 > C3 BUT ____< D3
THEN B1=E3 etc etc etc

End IF
Thank you SO much!
Michelle

Dear Michelle

I wrap you condition to understand easily, but I don't the cellls reference in the red blank underline. Please Fill it.

Another question is which cell that you want to place the formula? Please define.

**vs4hire** · 07-07-2007, 01:29 PM

I've attached an image of what I would like to have happen - hope it helps!

Michelle

**vs4hire** · 07-08-2007, 10:34 PM

Anyone have even a suggestion?

Thanks!
Michelle

**rylo** · 07-09-2007, 01:16 AM

Michelle

B1: =IF(ISNA(VLOOKUP(A1,$C$1:$E$5,3)),"ERROR",VLOOKUP(A1,$C$1:$E$5,3))

Copy down to B3.

You don't really need the column D data. If you remove it, you can use the VLOOKUP over 2 columns.

HTH

rylo

**vs4hire** · 07-12-2007, 09:43 PM

I couldn't get it to work properly, but thank you for your efforts...

I think at this point it would be easier to just pay someone to take a look at it.

Again, thank you for your help though!
Michelle