FORMULA

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
the only way around it is to combine cells...so run an IF over more than once cell...

so you last (if true) in the first cell (eg A1) would be B1

in B1 you then continue the if - so the result will eventually be returned in B1 and therefore picked up in A1.

Would suggest you post what you're trying to do here - I'd put money on it that someone can create a different formula that will do the trick!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
A lookup formula should accomplish what you're asking. Repost (stay in this thread) with the exact layout of the data in your workbook (worksheet names, columns, rows, etc. involved) along with a small sample and expected results.
 

ddragas

Board Regular
Joined
Jun 11, 2002
Messages
83
It is a litle bit complicated, but I'll try to explain

It's all about discount (%)

I've got 15 conditions.
Cell D1 is costumer
Range("B15:B39) are diferent Codes of products

Now let's start
If(Vlookup(B15;Šifre!B2:G65535;6)="P";If(Vlookup(B15;Kupci!B2:M65535;12)="I";KOEFICIJENTI!D12))))

there are letters that specify tipe of article:
P
D
Š4
R3
K

and tipe of costumer:

I
R
E

All together 15 conditions.

Every product has a diferent discount depending of costumer.

Until now 25000 products, and 700 costumers.



Or to resolve problem with a macro?
Something based on folowing:


Dim fn As WorksheetFunction, LookupVal As Variant
Set fn = WorksheetFunction
On Error Resume Next
If fn.VLookup(Sheets("Radno").Range("B15"), Sheets("Šifre").Range("b:g"), 6, False)="P" And
If fn.VLookup(Sheets("Radno").Range("D1"), Sheets("Kupci").Range("b:m"), 12, False) = "I" Then
Sheets("Radno").Range("F15") = Sheets("KOEFICIENTI").Range("D12")


Hope U've understood me.
THNX
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

On 2002-11-07 08:09, ddragas wrote:
It is a litle bit complicated, but I'll try to explain

It's all about discount (%)
[..]

Not sure what follows can be of any help to you... I cooked up the example by guessing what you need...
Book5
ABCDEF
1CustomerTypeArticleTypeDiscount
2t1-@-a1t1a12%
3t1-@-a2t1a22.30%
4t1-@-a3t1a33%
5t2-@-a3t2a33.30%
6t1-@-a4t1a44%
7t3-@-a4t3a44.50%
8
9LightgreenareaisnamedasTable.
10
11CustomerQuantityCustomerTypeArticleTypeDiscount?
12Cust140t1a30.03
13
Sheet1


Hope this helps.
 

Dejan

New Member
Joined
Oct 18, 2002
Messages
24
It should go like this:<table x:str border=0 cellpadding=0 cellspacing=0 width=929 style='border-collapse:
collapse;table-layout:fixed;width:697pt'><col width=64 span=2 style='width:48pt'><col class=xl24 width=84 style='mso-width-source:userset;mso-width-alt:3072;
width:63pt'><col width=64 span=3 style='width:48pt'><col width=77 style='mso-width-source:userset;mso-width-alt:2816;width:58pt'><col class=xl24 width=64 style='width:48pt'><col width=64 span=6 style='width:48pt'><tr height=17 style='height:12.75pt'><td height=17 class=xl31 colspan=2 width=128 style='height:12.75pt;
mso-ignore:colspan;width:96pt'>Customers</td><td class=xl27 width=84 style='width:63pt'> </td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td class=xl32 width=64 style='width:48pt'>Products:</td><td class=xl26 width=77 style='width:58pt'> </td><td class=xl26 width=64 style='width:48pt'> </td><td width=64 style='width:48pt'></td><td class=xl35 width=64 style='width:48pt'> </td><td class=xl38 width=64 style='border-left:none;width:48pt'>I</td><td class=xl38 width=64 style='border-left:none;width:48pt'>R</td><td class=xl38 width=64 style='border-left:none;width:48pt'>E</td><td class=xl31 width=64 style='width:48pt'>Customers</td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>1</td><td class=xl27>Mike</td><td class=xl28>I</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>1000</td><td class=xl25>AirCondition</td><td class=xl26>P</td><td></td><td class=xl37 style='border-top:none'>P</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.1">10%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.15">15%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.2">20%</td><td></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>2</td><td class=xl27>John</td><td class=xl28>R</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>1100</td><td class=xl25>Exaust</td><td class=xl26>D</td><td></td><td class=xl37 style='border-top:none'>D</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.08">8%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.05">5%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.15">15%</td><td></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>3</td><td class=xl27>Peter</td><td class=xl28>E</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>1200</td><td class=xl25>Burner</td><td class=xl26>R3</td><td></td><td class=xl37 style='border-top:none'>R3</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.15">15%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.1">10%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0">0%</td><td></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>4</td><td class=xl27>Mark</td><td class=xl28>E</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>1300</td><td class=xl25>Boiler</td><td class=xl26>Š4</td><td></td><td class=xl37 style='border-top:none'>Š4</td><td class=xl36 style='border-top:none;border-left:none' x:num="0">0%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.05">5%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.15">15%</td><td></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>5</td><td class=xl27>Jorge</td><td class=xl28>I</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>1400</td><td class=xl25>ScrewDriver</td><td class=xl26>K</td><td></td><td class=xl37 style='border-top:none'>K</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.2">20%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.05">5%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.1">10%</td><td></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>6</td><td class=xl27>Michael</td><td class=xl28>R</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>6</td><td class=xl25>etc....</td><td class=xl26>D</td><td></td><td class=xl32>Products:</td><td colspan=4 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>7</td><td class=xl27>etc......</td><td class=xl28> </td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>7</td><td class=xl25>etc....</td><td class=xl26>R3</td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>8</td><td class=xl27>etc......</td><td class=xl28> </td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>8</td><td class=xl25>etc....</td><td class=xl26>Š4</td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 colspan=2 style='height:12.75pt;mso-ignore:colspan'></td><td class=xl24></td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>9</td><td class=xl25>etc....</td><td class=xl26> </td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 colspan=2 style='height:12.75pt;mso-ignore:colspan'></td><td class=xl24></td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>10</td><td class=xl25>etc....</td><td class=xl26> </td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 colspan=2 style='height:12.75pt;mso-ignore:colspan'></td><td class=xl24></td><td colspan=4 style='mso-ignore:colspan'></td><td class=xl24></td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 colspan=2 style='height:12.75pt;mso-ignore:colspan'></td><td class=xl24></td><td colspan=4 style='mso-ignore:colspan'></td><td class=xl24></td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl33 colspan=2 style='mso-ignore:colspan'>WORKING AREA:</td><td class=xl34 align=right x:num>4</td><td class=xl34
>Mark</td><td colspan=9 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl29> </td><td class=xl30> </td><td colspan=11 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl29> </td><td class=xl30> </td><td colspan=11 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30>Code</td><td class=xl30>Article</td><td class=xl30>Quantity</td><td class=xl30>Discount</td><td colspan=9 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30 x:num>1000</td><td class=xl30
AirCondition</td><td class=xl30 x:num>1</td><td class=xl29 colspan=2 style='mso-ignore:colspan'>Here goes formula:</td><td colspan=8 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30 x:num>1100</td><td class=xl30
Exaust</td><td class=xl30 x:num>5</td><td class=xl29 colspan=10 style='mso-ignore:colspan'>IF(VLOOKUP(B19;$F$2:$H$11;3)="D";AND(IF(VLOOKUP($D$14;$A$2:$C$9;3)="I";$K$3)
etc.............)))</td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30 x:num>1200</td><td class=xl30
Burner</td><td class=xl30 x:num>8</td><td colspan=10 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30 x:num>1300</td><td class=xl30
Boiler</td><td class=xl30 x:num>3</td><td colspan=10 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30 x:num>1400</td><td class=xl30
ScrewDriver</td><td class=xl30 x:num>4</td><td colspan=10 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl29> </td><td class=xl30> </td><td colspan=11 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl29> </td><td class=xl30> </td><td colspan=11 class=xl29 style='mso-ignore:colspan'> </td></tr><![if supportMisalignedColumns]><tr height=0 style='display:none'><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=84 style='width:63pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=77 style='width:58pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td></tr><![endif]></table></body></html>


Sory For taken space. HtmlMaker2.32 doesnt work on my machine.
This message was edited by Dejan on 2002-11-07 12:00
 

Forum statistics

Threads
1,144,310
Messages
5,723,632
Members
422,505
Latest member
Noar33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top