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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Maybe something like

=VLOOKUP(C2;CHOOSE(H2;PointsFTG!A:E;PointsPrivat!A:E);4;FALSE))

It would make more sense if you told us what was in H:H, L:L, C:C, and how each of those relates to what you're trying to do.
 
Upvote 0
H:H is pID for seller and L:L is pID for what kind of store.
C:C is the article name for a sold produkt. This is where the VLOOKUP-function pickups the value from in the different sheets of scores depending on pID.

There's two kinds of salesmen and two kinds of stores.
So, sometimes a salesman with pID 1 works in a pID 2 store. Which means that the seller should have a score from the pID 2 sheet (PointsFTG).
And sometimes a salesman with pID 2 works in a pID 1 store. Which means that the seller should have a score from the pID 1 sheet (PointsPrivat).
Then also can a salesman with pID 2 work in a pID 2 store. Which means that the seller should have a score frmo the pID 2 sheet (PointsFTG).

haha.. tried to be so specific as possible there to help you guys out.
Tried those two formulas out and it didn't work out.

Maybe I can send someone of you the file and you'll see directly?
 
Last edited:
Upvote 0
There's two kinds of salesmen and two kinds of stores.
So, sometimes a salesman with pID 1 works in a pID 2 store. Which means that the seller should have a score from the pID 2 sheet (PointsFTG).
And sometimes a salesman with pID 2 works in a pID 1 store. Which means that the seller should have a score from the pID 2 sheet (PointsFTG).
Then also can a salesman with pID 2 work in a pID 2 store. Which means that the seller should have a score from the pID 2 sheet (PointsFTG).
Then a salesman with pID 1 can work in a pID 1 store. Which means that the seller should have a score from the pID 1 sheet. (PointsPrivat)
 
Upvote 0
The first cell becomes #N/A and the pID 2 should be to PointsFTG. Right now it takes from the PointsPrivat... =(

There's two kinds of salesmen and two kinds of stores.
So, sometimes a salesman with pID 1 works in a pID 2 store. Which means that the seller should have a score from the pID 2 sheet (PointsFTG).
And sometimes a salesman with pID 2 works in a pID 1 store. Which means that the seller should have a score from the pID 2 sheet (PointsFTG).
Then also can a salesman with pID 2 work in a pID 2 store. Which means that the seller should have a score from the pID 2 sheet (PointsFTG).
Then a salesman with pID 1 can work in a pID 1 store. Which means that the seller should have a score from the pID 1 sheet. (PointsPrivat)
 
Upvote 0
taken said:
No, that didn't work at all actually.
Click to expand...

Here all is ok. Look at this:

Col CResultCol HCol L
Product01Private0121
Product02FTG0222
Product01FTG0112
Product02Private0211
**********************************************

<tbody>
</tbody>

Could you post a small example of your data and the formula?

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Tallying list overlap
Replies
1
Views
275
JamesCanale
JamesCanale
E
  • Question
Alternative to complex SumProduct to rank data by specific variable
Replies
3
Views
565
Peter_SSs
Peter_SSs
MDHolmes
  • Solved
Populating a wage based on a drop-down list
2
Replies
13
Views
1K
MDHolmes
MDHolmes
E
  • Question
VBA growing a tree from a seed to use DFS to traverse
Replies
0
Views
2K
equinox
E
M
  • Question
Excel Check Employee Rates
Replies
0
Views
980
Megsnd
M

Forum statistics

Threads
1,214,895
Messages
6,122,128
Members
449,066
Latest member
Andyg666

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