I have created the following formula, but it is not working properly, it Shows an error "Circular Reference Warning".



=VLOOKUP(B2,$A$10:$B$13,2,FALSE)+(VLOOKUP(D2,$B$10 :$C$13,2,FALSE)*C2)

So, like in the title, please can you look at the image or the excel file, you will know what I mean.

I want to calculate: First find the department's salary
(ex. B2 is dept 200, which has salary of 10000 (from B11) )
and to the salary add the commission (C11) which should be multiplied by Sales (C2). This formula has to use Lookup function!!!

It is for the class assignment, so it has to contain Lookup formula.

Thanks

Attached Images

lookup formula.jpg (99.9 KB, 16 views)

Attached Files

The file.xlsx (11.1 KB, 16 views)

You say you want to put in D2 a double VLOOKUP function, that is, VLOOKUP(B2, blah, blah) + VLOOKUP(D2, blah, blah). But check it out: You want to put in D2 a formula that refers to D2! That's where the circularity comes in.

Well, I guess you are smarter than me, that's why I need someone's help. All I am trying to do is to: Salary plus (commission multiplied by percent). I am not sure any how to do it. I also tried to combine "IF FUNCTION" with Vlookup, but it failed.

Can someone help me on this problem? please

Ok, so anyone in department 200 gets a salary of $10K plus a commission of 2%, right? But if I follow this, that commission is 2% of some other number, not of $10K but of that worker's gross sales or something. Oh, wait, I see it; the sales figure is in C2 through C5.

So in D2 through D5 you need to calculate each employee's take—which is his salary (from C10 through C13) plus his percentage commission (in D10 through D13) times his sales (in C2 through C5). Your formula, let's see....is right except it uses D2 instead of B2 in the second VLOOKUP.

Now, your first reply shows you're reluctant to stop and think about this, so if you don't understand the above, I'll stop here and wait for you to put forth some effort. (Sound just like your teachers, don't I? Sorry, that's how it is.) I won't stop answering your questions; but you gotta ask good ones, which means you have to think hard about what you're trying to do and what it is that's holding you up.

Oh, and just so you don't get discouraged, that doesn't mean you can't stop, back up and start from the beginning. It sounds like you understand what salary, sales and commission are and how they work together, for example; but if not, ask.

So it is necessary?

Not sure what you mean, Serge. Is what (exactly) necessary in order to accomplish exactly what?

Does topgear really need need to complete his class assignment? Gee, I dunno :-).
Is a VLOOKUP necessary to satisfy the assignment? Topgear says it is.
Is it necessary to change D2 to B2 in order to fix the "circular" problem? Yes, it is.
Is this exact formula the only way to do the lookup? Very doubtful; there are almost always multiple ways to do the same thing.

I note, by the way, that your code fixes a problem in topgear's formula that I missed; it checks the correct table area in the second VLOOKUP. Well done, I didn't notice it myself, so my solution, just changing D2 to B2, while necessary, would still not have done the job.

Sorry, I don't speak English

And I have almost no Russian at all. Mais je suis confortable en le français, y puedo escribir y leer español, och kan jag läsa svenska (lite), and I probably wouldn't die if I had to figure out something you write in italiano, portugues, esperanto o interlingua. Any of those help?

Unfortunately I know only Russian
I use the Internet translators, but they very badly translate
It not offtop?

I am not trying to make anybody do assignment for me!!! Let me make this absolutely clear. Obviously, this formula can also contain other functions like "IF funciton" or "Sum function". I have had 9 other assignments that I have already done similar to this, but easier, this is my last one, on which I am confused. I tried to replace D2 with B2, but it did not seem to work. Do you guys have other suggestions? Sometimes I think you just don't want to help me, maybe some tips at least, please?

Yeah, it's more fun just to mock you . No, seriously, topgear; what I said about replacing D2 with B2 was right as far as it went, but it didn't go far enough; I didn't notice at first that there was another problem too. Take a look at the formula in Serge's first post, in that Code box; it used my D2/B2 substitution but also corrected the other error too. I think that should work for you.

Having paused to make that crack about mocking you, let me back up and reiterate what I said about your having to think about this. I accept that you're confused, really I do; but while you may have thought about it, you haven't put any of your thoughts into words, explained what you think should happen and what's happening instead, asked what the parts of the VLOOKUP function do, or anything else.

If you're willing, even if Serge's formula works for you, maybe it would help to go over a VLOOKUP that isn't part of your assignment, talking about each piece of it so you'll be comfortable with it next time. VLOOKUP is one of the functions I use a lot in Excel, and I wouldn't be surprised if you do too; so it's well worth spending some time on to learn it well.

OK, I have figured it out, and I fixed it!!!!!!!!!!!!!!!!!!!! yeah, no, really I did. I cannot post the code, the function, in case of the professor checking something online for plagiarism or something. Whatever! OK, thanks.