VLOOKUP - N/A error

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

Hi,

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

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

Hope it helps

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, 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.

Originally Posted by protonLeah

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.

Originally Posted by FDibbins

dramadeur, welcome to the forum

Thanks.

Originally Posted by FDibbins

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.

Originally Posted by FDibbins

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.

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?

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?

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

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)

Originally Posted by dramadeur

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

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

Formula:

Please Login or Register  to view this content.

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

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)

Originally Posted by humdingaling

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.

Originally Posted by humdingaling

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.

Originally Posted by humdingaling

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.

Originally Posted by FDibbins

vlookup cannot use non-contiguous ranges like that.

My suspicion has been confirmed:

Originally Posted by dramadeur

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

Originally Posted by FDibbins

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.

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

Formula:

Please Login or Register  to view this content.

Originally Posted by humdingaling

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:

Originally Posted by humdingaling

[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...

Originally Posted by FDibbins

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.

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

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

Formula:

Please Login or Register  to view this content.

alternatively you can just manually type in the array

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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