VLOOKUP - N/A error

[dramadeur]

Alright, so in a new column I want to show a "discount price" that will multiply the value of cost by 0.5 (50% discount) if the item is available online.
For instance, the very first item (product id: 312-0791) should be sold at 110*0.5 =55 $
I'm trying to do it via vlookup (I know how to do it using another formula, so pls don't suggest irrelevant advices).
The file, and a screenshot (btw, Q4 contained the word "YES").

Even basic lookup didn't work out, why? Maybe ranges get messed up? (YES/NOs in the column I)
It needs to be done without removing any columns. I reckon, I could do it by adding additional columns, but maybe perhaps someone from this forum would be able to solve it without the addition of extra columns?

products.xlsx

[canapone]

Hi,

a different approach could be a formula in M2 to be copied down

=F2*IF(I2="YES",0.5,1)

Hope it helps

[dramadeur]

Hope it helps

No it doesn't, re-read the op-post.

[protonLeah]

To attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

You need to edit your post and delete line #3! Its not polite to tell volunteers working on their own time what you will or will not accept.

[dramadeur]

You need to edit your post and delete line #3! Its not polite to tell volunteers working on their own time what you will or will not accept.

I said what I need, anything else is irrelevant and doesn't answer my question. Period.
As for politeness, it's not polite to ignore what OP says.
Besides, giving a solution with an alternative method that I had already said I'm aware of is kinda pointless. I obviously wouldn't have created the thread if I needed it to be done with a different formula. My topic states specifically what formula I'm having trouble with. So it's not like I'm not "accepting answer" without making it clear beforehand what kind of solution I need.
I'd rather get no answer, than a bogus one, which is being given out of sheer ignorance/or by ignoring what I had asked for specifically.

dramadeur, welcome to the forum

Thanks.

That is kind of a short-sighted (and rude?) view of things?
as is your reply in post #3...

I said straightforwardly why his answer won't work for me. I fail to see any rudeness in it, for there are no pejorative words that might offend him. It's impolite to give an irrelevant answer, considering the clarity of the question.

2. Please upload a sample of your workbook - to the forum, not a file-hosting site, and not a picture of your data, either. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

This is why I attached a file. Would it hurt to have a screenshot available? Many pros can figure out a problem off the bat, without having to edit the file, just by looking at a screenshot that depicts a formula and data input.
Plus, it's easier for me, and better for your servers if users upload it elsewhere. Besides, it is simpler, because even non-registered users can see it (or those who haven't logged in).

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

It already does, it actually is much bigger, and I'm supposed to reference the columns in VLOOKUP from another sheet.

[FDibbins]

dramadeur, welcome to the forum

A few observations....
1.

I know how to do it using another formula, so pls don't suggest irrelevant advices)

That is kind of a short-sighted (and rude?) view of things?
as is your reply in post #3...

No it doesn't, re-read the op-post.

2. Please upload a sample of your workbook - to the forum, not a file-hosting site, and not a picture of your data, either. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[FDibbins]

This is why I attached a file.

Perhaps you missed the part where I said "2. Please upload a sample of your workbook - to the forum, not a file-hosting site"

Also, please keep in mind that politeness is the order of the day here.

and I'm supposed to reference the columns in VLOOKUP from another sheet.

This seems to indicate that this is a homework assignment?

[dramadeur]

Perhaps you missed the part where I said "2. Please upload a sample of your workbook - to the forum, not a file-hosting site"

Then I shall abide by your rules, your excellency.

Also, please keep in mind that politeness is the order of the day here.

Ok, but I fail to see why I need to hear it.

This seems to indicate that this is a homework assignment?

Not necessarily. More like that it is actually much more complex.

[humdingaling]

Not necessarily. More like that it is actually much more complex.

if it is more complex then you need to explain how/why it is going to be more complex as the solution provided may not suit the purpose for your more complex requirements

also asides from assignment...when does anyone have to be forced into one specific solution when there is an obvious better one (that was given)


any who...solution with VLOOKUP attached ...however...it required extra data input (cell R4)
in that regard i attached a solution with LOOKUP that does not require additional data input

[dramadeur]

if it is more complex then you need to explain how/why it is going to be more complex as the solution provided may not suit the purpose for your more complex requirements

ah no, no need to worry about it, it'll be my problem how to alter it. No really, if it works for the file attached, then it is indeed what I need.

any who...solution with VLOOKUP attached ...however...it required extra data input (cell R4)

Uhm... it should start with VLOOKUP, "if statement" can be inside it. But not precede it.
I will try to play around with it. Thanks for the heads up.
Major culprit is the range, it gets messed up. Pretty much sure now that the solution would involve adding new columns that would define the ranges.

in that regard i attached a solution with LOOKUP that does not require additional data input

Thanks, but this was unnecessary, as I'm saying it for the third time already, I only need the solution with the vlookup function.

vlookup cannot use non-contiguous ranges like that.

My suspicion has been confirmed:

Maybe ranges get messed up? (YES/NOs in the column I)

humdingaling has offered a suggestion that gives you what you need, but it is so involved compared to what you actually need as to be almost (words fail me)

Perhaps, it can be done with vlookup, just additional columns must be added (with certain ranges). I will work on that.

[FDibbins]

OK let me spell it out for you clearly.

Not all members are willing - or able - to access file-hosting sites, or are all members able - or willing - to view pictures of your data...and even if they can, why should they have to recreate your data for you, when you can just upload a sample workbook to the forum.

You want help - help us to help you, simple as that

And again I ask - is this a homework assignment?

[dramadeur]

OK let me spell it out for you clearly.

Not all members are willing - or able - to access file-hosting sites, or are all members able - or willing - to view pictures of your data.

I had already done it before you posted this post.

and even if they can, why should they have to recreate your data for you

It's one of the reasons the link to the file had been provided by me.
By your first order to attach it to this site I obliged.

And again I ask - is this a homework assignment?

No.

[FDibbins]

Thank you for the file. Hopefully a member will offer some suggestions to you

[FDibbins]

OK I have looked at your file, and dont see why you feel the need to use vlookup?

vlookup is used to search down a list if items for a specified item, and then to return a corresponding value from a defined column. Sor in the example below...

	A	B
1	a	6
2	b	5
3	c	4
4	d	3
5	e	2
6	f	1
7
8
9	c	4

A9 would be what you are looking for, and you want the info from the 2nd column. So the formula and syntax would be...
=VLOOKUP(A9,A1:B6,2,0)

However, I dont see that this is that kind of requirement. You are testing the contents of a single cell - row-by-row - for the presence of "Yes", and if found, you want to take only 1/23 the price.

Looking at what (I think) you want, canapone's suggestion in post # 2 will give you exactly what you want.

=F2*IF(I2="YES",0.5,1)

[dramadeur]

OK I have looked at your file, and dont see why you feel the need to use vlookup?

If you need to know the reason as to why I need to use vlookup function before helping me with it, then no problem, I can say it.
Because I want to see how vlookup can be implemented here in order to do what needs to be done in a new column designated for the discounted prices.
So I guess it's curiosity.

vlookup is used to search down a list if items for a specified item, and then to return a corresponding value from a defined column.

yes, I know.
Why can't it return the price from the first column, if the corresponding cell from the second column matches up for the "YES" value?

Looking at what (I think) you want, canapone's suggestion in post # 2 will give you exactly what you want.

I wonder if I respond to this part of your post by stating the obvious, would I be again accused of something that is supposed to be socially unacceptable?

I really don't know how to make it clearer that I need it work only by the use of the formula vlookup.
It can be used with the if statement inside it, but it has to be vlookup.
Does anyone know how to do it with the vlookup function? If yes, say it, if no - then be aware that it's not what the question of this thread is asking for.

[humdingaling]

if you insist of using vlookup within a formula for the sake of having vlookup in the formula...

=IF(VLOOKUP(I2,$I$2:$I$29,1,0)="YES",0.5,1)*F2

which is basically solution in post 2 with vlookup in there for the sake of having it in there

[FDibbins]

I really don't know how to make it clearer that I need it work only by the use of the formula vlookup.
It can be used with the if statement inside it, but it has to be vlookup.
Does anyone know how to do it with the vlookup function? If yes, say it, if no - then be aware that it's not what the question of this thread is asking for.

It is apparent from the above that you really dont have a grasp of how vlookup works, despite your claims to the contrary
=VLOOKUP(what-you-want-to-find, Range-to-search-in, column-to-return-answer-from, TRUE-for-sorted-data-or-FALSE-for-exact-match)
The what-you-want-to-find generally needs to be in the 1st column of the search-range

You are not using a table, you are testing 1 cell in a row, and basing a calculation on that test - a simple IF() statement...
IF contents of cell = Yes, do this calc, else do that calc.

The syntax of 2 formulas you have presented in your workbook is incorrect
=VLOOKUP(Q4,($F$2:$F$10,$I$2:$I$10),2,TRUE)
=VLOOKUP("YES",($F$2:$F$29,$I$2:$I$29),IF(I2="YES",1*0.5,1),TRUE)
vlookup cannot use non-contiguous ranges like that.

humdingaling has offered a suggestion that gives you what you need, but it is so involved compared to what you actually need as to be almost (words fail me)

[humdingaling]

with Vlookup first without changing anything.......

=VLOOKUP(I2,IF(I2="YES",CHOOSE({1,2},I2,F2*0.5),CHOOSE({1,2},I2,F2)),2,0)

[dramadeur]

with Vlookup first without changing anything.......
CHOOSE

I tried to do it just with the included ifs instead, but it didn't work. Is there a way to use the regular formulas along the vlookup? "choose" seems unfamiliar to me.
Also, what do these braces:

[FORMULA]=CHOOSE({1,2},I2,F2*0.5)

{} - mean? It seems that in this case the formula "choose" always returns the first value, which is I2.
If you put: "{2,1} in these braces, then it will return the second value, which is F2*0.5

BTW, does anyone know why vlookup stops working if for table array parameter you set two different columns that have a gap? I thought parentheses solve this issue of including columns that are separated by a gap...

The syntax of 2 formulas you have presented in your workbook is incorrect
=VLOOKUP(Q4,($F$2:$F$10,$I$2:$I$10),2,TRUE)
vlookup cannot use non-contiguous ranges like that.

So is that it? No way to add "non-contiguous ranges" to "table array" parameter?



The screenshot, file is attached.

[FDibbins]

The more I think about this, the more it seems like you are trying to add 1 + 1 but doing it by adding 47 + 55 -100

[humdingaling]

normally you wouldnt need to use choose in the manner i showed in my previous post but you insisted vlookup be in the equation and be first
that whole if loop is just to facilitate that

i redid the formula without CHOOSE but IF instead

=VLOOKUP(I2,IF(I2="YES",IF({1,0},I2,F2*0.5),IF({1,0},I2,F2)),2,0)

alternatively you can just manually type in the array

=VLOOKUP(I2,{"YES",0.5;"NO",1},2,0)*F2

Re:
So is that it? No way to add "non-contiguous ranges" to "table array" parameter

=VLOOKUP("yes",IF({1,0},W2:W3,T2:T3),2,0)

yes you can ....you can even do it right to left but not in the manner you want in your example

oh the {}
http://office.microsoft.com/en-au/ex...001087291.aspx