Complicated IF's

T

taken

Board Regular
Joined
Dec 14, 2012
Messages
55
Don't know if there's a better way of doing this.
Thus nested if-statements has a max value of seven and I'm afraid that this code will be more then that.

Making a sheet of points depending on what a seller has sold.
I've got VLOOKUP-functions fetching data adding points to specific articles.

My problems comes when one article has different scores depending on who sold it.
Therefore I need to make an if-statement looking for the value 1 and 2 and making a VLOOKUP to 1 or 2's specific score sheet.

I really don't know how to solve this one.
Probably it's easier if someone just looks at the file, but there's no possibility to upload it here.

I've got this so far:
=IF(H:H=1&L:L=2;VLOOKUP(C:C;PointsFTG!A:E;4;FALSE);VLOOKUP(C:C;PointsPrivat!A:E;4;FALSE))

Don't know if it's possible to have that beginning in the IF-statement.

Is there any Excel expert out there willing to help me with this?
Thanks!
/ Martin
 
Sort by date Sort by votes
Artikel-nrAntalDatumSäljarepIDPoängButikpID
TM K 0,5GB 12m uP12014-06-12Johanna210Luleå1
TM K 2GB 24 mån12014-06-03Johanna25Luleå1
JM Multi30 12m uP12014-06-10Johanna210Luleå1
JM Bas 12m uP12014-06-10Johanna25Luleå1
JM Rörlig 0m uP12014-06-03Johanna210Luleå1
Bokatgnf12014-06-03Johanna210Luleå1

<tbody>
</tbody>


In this case the seller has a pID 2 where the store pID is 1. This should look on the pID 2 sheet of scores (PointsFTG).
If I understand this correctly the code is missing to check if the seller pID is 2 and of store pID is 1. Then it should go for the PointsFTG.
 
Last edited:
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Also I got this at the top row..

It's a #N/A which I don't understand why. Here's the score (poäng) correct.

Artikel-nrAntalDatumSäljarepIDPoängButikpID
TM K 0,5GB 24 mån1
2014-06-10
Christoffer Tangen1
#N/A
Malmö C1
TM Mobansl 24 mån12014-06-13Markus Olsson11Karlshamn1
TM Mobansl 24 mån12014-06-10Marie Claesson15Karlshamn1
TM K 0,5GB 12m uP12014-06-10Marie Claesson15Karlshamn1
TM K 0,5GB 24 mån12014-06-10Markus Olsson11Karlshamn1
TM Mobansl 24 mån12014-06-09Marie Claesson11Karlshamn1

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Artikel-nr
AntalDatumSäljare
pID
PoängButikpID
TM K 0,5GB 12m uP12014-06-12Johanna Malmström
2
 0
Luleå1
TM K 2GB 24 mån12014-06-03Johanna Malmström2 0
Luleå1
JM Multi30 12m uP12014-06-10Johanna Malmström2 0
Luleå1
JM Bas 12m uP12014-06-10Johanna Malmström2 0
Luleå1
JM Rörlig 0m uP12014-06-03Johanna Malmström2 5
Luleå1
Bokatgnf
12014-06-03Johanna Malmström2 0
Luleå1
Bokatgnf
12014-06-05Johanna Malmström2 0
Luleå1
Bokatgnf
12014-06-07Johanna Malmström2 5
Luleå1
Bokatgnf
12014-06-10Johanna Malmström2 0
Luleå1
Bokatgnf
12014-06-10Johanna Malmström2 0
Luleå1
Bokatgnf
12014-06-10Johanna Malmström2 0
Luleå1
Bokatgnf12014-06-12Johanna Malmström2 0
Luleå1
Bokatgnf
12014-06-12Johanna Malmström2 0
Luleå1
Bokatgnf
12014-06-13Johanna Malmström2 0
Luleå1
TM Surfans 24 mån
12014-06-13Johanna Malmström2 5
Luleå1
TM Surfans 24 mån
12014-06-12Johanna Malmström2 0
Luleå1
TM Surfans 24 mån
12014-06-03Johanna Malmström2 5
Luleå1
SM-T3150ZWANEE12014-06-13Johanna Malmström
2 0
Luleå1

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

It seem to work sometimes, and sometimes not. At the SM-T3150 row it says 0 when it suppose to say 10. Cause, these points are the same and is the same in both sheets.
And if you look at Bokatgnf there's some with 5 and some with 0. All of them should be 5...? Don't understand why just one got the point..
 
Upvote 0
WOOOW!!!

Now it's working.. I changed the code a little bit. So, now it's all fine.

=IF(AND(H2=2;L2=1);VLOOKUP(C:C;PointsFTG!A:E;4;FALSE);VLOOKUP(C:C;PointsPrivat!A:E;4;FALSE))

Just another thing which will take me forever with nested if-statements I'm afraid.

If a seller makes two of one article it doesn't multiply the point. And also if he/she takes something back it should be a minus in the point.
Earlier I did an IF-statement like this...

=IF(E:E<=-2;VLOOKUP(C:C;PointsPrivat!A:D;4;FALSE)/-1*2;IF(E:E<=0;VLOOKUP(C:C;PointsPrivat!A:D;4;FALSE)/-1;IF(E:E>=2;VLOOKUP(C:C;PointsPrivat!A:D;4;FALSE)*Data!E:E;VLOOKUP(Data!C:C;PointsPrivat!A:D;4;FALSE))))

But, is there any better solution for it?

Beskrivning
AntalDatumSäljarepID
Point
Komplett 0,5GB 24 mån m Pr, TM K 0,5GB 24 mån
12014-06-10Christoffer Tangen1
 1
Mobilan****ning 24 mån m Pr, TM Mobansl 24 mån
2
2014-06-13
Markus Olsson1 5 (10)
Mobilan****ning 24 mån m Pr, TM Mobansl 24 mån12014-06-10Marie Claesson
1 5
Komplett 0,5GB 12 mån u Pr, TM K 0,5GB 12m uP12014-06-10Marie Claesson1 1
Komplett 0,5GB 24 mån m Pr, TM K 0,5GB 24 mån
12014-06-10Markus Olsson1 1
Mobilan****ning 24 mån m Pr, TM Mobansl 24 mån
-1
2014-06-09
Marie Claesson
1 -5
Komplett 6GB 24 mån m Pr, TM K 6GB 24 mån12014-06-05Hanna Winsö1 10
Komplett 2GB 24 mån m Pr, TM K 2GB 24 mån12014-06-05Hanna Winsö1 10

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
... no, it's not working!

When seller is pID 2 and store is pID 2 it fetches the point from the pointPrivat...
But, everything else is working like a charm. Is it anything missed in the if-statement?
The statement becomes FALSE and that's why, I guess. Cause, the FALSE-value is connected to the PointsPrivat?


Col CResultCol HCol L
Product01Private0121
Product02
FTG0222 ..fetching data from pointsPrivat
Product01FTG0112
Product02Private0211
**********************************************

<tbody>
</tbody>
 
Last edited:
Upvote 0
That is not working... got an error message that the formula contains error! =(

Could I send you the file via email and you'll have a look on it?
 
Last edited:
Upvote 0
That was the problem =))

Now it look a lot better!

Just another thing.
In that formula. How to multiply the units sold with the point?
Also, if it's a -1 or more in the sold units (antal) it should of course say minus in the point.
 
Upvote 0
I used this one before... but, this can't work now.
With that new working formula thanks to you this one wont work!

=IF(E:E<=-2;VLOOKUP(C:C;PointsPrivat!A:D;4;FALSE)/-1*2;IF(E:E<=0;VLOOKUP(C:C;PointsPrivat!A:D;4;FALSE)/-1;IF(E:E>=2;VLOOKUP(C:C;PointsPrivat!A:D;4;FALSE)*Data!E:E;VLOOKUP(Data!C:C;PointsPrivat!A:D;4;FALSE))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Tallying list overlap
Replies
1
Views
302
JamesCanale
JamesCanale
E
  • Question
Alternative to complex SumProduct to rank data by specific variable
Replies
3
Views
582
Peter_SSs
Peter_SSs
MDHolmes
  • Solved
Populating a wage based on a drop-down list
2
Replies
13
Views
2K
MDHolmes
MDHolmes
R
  • Question
Golf scoring game
Replies
4
Views
129
Rock5150
R
E
  • Question
VBA growing a tree from a seed to use DFS to traverse
Replies
0
Views
2K
equinox
E

Forum statistics

Threads
1,216,102
Messages
6,128,852
Members
449,471
Latest member
lachbee

Share this page

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
Back
Top