Complex IF Formula

Hi,

From your description it's almost certain that a VLOOKUP is precisely what you need. Build yourself a two column table with 1,2,3, etc. in the first column, and 90%, 80%, 70% or whatever alongside in the adjacent column. Name the table say 'MyTable'

Now use the formula

Please Login or Register  to view this content.

HTH

You can put the VLOOKUP table on another tab or off to the side somewhere and hide those columns. If your rules don't change, the table won't change and you can forget about it. With 2003, you'd be hard pressed to find a solution within a single cell and it would be very ugly.

Originally Posted by davo1224

Ah I see. I was hoping to contain the whole process in one cell. Thanks.

You can contain it within one cell, by hard coding your table array.Write the formula as earlier advised with the vlookup, and for the first part make up a table with your 1-10 values in one column, and the values to return in the column next to it. once you've written the formula, highlight the cells representing the table and hit F9 you'll get a similar result to this

VLOOKUP(F3,{1,90;2,80;3,70;4,60;5,50;6,40;7,30;8,20;9,10;10,0},2,0)

F3 houses the Vlookup value, the numbers within the { } are your array of values and will return 90 for 1 80 for 2 etc etc

Yeah I'm just going to put it further away from the visible area.

It ended up being exactly what I needed. Thanks.

Hi davo. you lost me. (not that hard). Take a look at the attached and see if its what you want. If you are saying that you specifically want the text written out in the next column, it would be (assuming name in A2, Catagory (2) in B2, Original $ in C2 and result in D2) in E2
="(if "&B2& ", then do "& TEXT(C2,"$0.00") & " x " & VLOOKUP(B2,Table,2,FALSE) & ")"

stealing the sample from the last poster, here's a version with an incell formula and no table displaying