DOUBT

[Sherlock Holmes]

WHENEVER I AM TRYING TO USE THE SUMIF FORMULA IN GOOGLE SHEETS, THE RESULT IS COMING ZERO, I CANNOT UNDERSTAND WHERE I AM GOING WRONG.

[Nikita Kumari]

Hi,

Can you send here a screenshot, so that we can have a look?

Thanks

Nikita

Statistics content support team

[Sherlock Holmes]

 Respected Madam,

 I tried once more and it worked fine. i am having difficulties in the vlookup function. I am attaching a Screenshot.

[Sherlock Holmes]

PLEASE ANSWER MY QUERY

[Anand Iyer]

Here's how the google defines its vlookup function.

Searches down the first column of a range for a key and returns the value of a specified cell in the row found. 

 

In your example, "Category" can't be found anywhere in the first column (A) of the range.

Instead, if you give this

=VLOOKUP("Category", B1:E6,2, FALSE) 

it'll work.  Try it.

[Nikita Kumari]

In vlookup function, it searches the key value(provided in 1st parameter) in the very first column of range provided. You provided search key as category which does not matches in 1st column of range provided ( A1) , that's why it shows error.

[Sherlock Holmes]

THANK YOU SO MUCH FOR YOUR HELP, EVERYONE. I HAVE UNDERSTOOD MY MISTAKE.

[Sherlock Holmes]

Respected Madam,

 i have given category as the first column in the range but strangely it is giving me the result qty

i am sending 2 screenshots.

[Sherlock Holmes]

please respond.

[Anand Iyer]

Use 1 as the first parameter to the vlookup function. 

--


You received this message because you are subscribed to a topic in the Google Groups "Discussion forum for Statistics for Data Science I" group.


To unsubscribe from this topic, visit https://groups.google.com/a/nptel.iitm.ac.in/d/topic/ma1002-discuss/X5AHfbVBgSw/unsubscribe.


To unsubscribe from this group and all its topics, send an email to ma1002-discus...@nptel.iitm.ac.in.


To view this discussion on the web visit https://groups.google.com/a/nptel.iitm.ac.in/d/msgid/ma1002-discuss/e576b216-07a3-456d-8387-da5fff244c0cn%40nptel.iitm.ac.in.

--

Cheers,

Anand

[Anand Iyer]

Sorry, not first, third parameter. Instead of 2

--

Cheers,

Anand

[Sherlock Holmes]

Its just returning the word Price. 

[Anand Iyer]

Hello detective,

Are you saying in the spreadsheet you used, when you used the formula?

=VLOOKUP("Category", B1:E6,1, FALSE)

you got 'Price'?

That can't happen. Can you please share your spreadsheet with me?

--

Cheers,

Anand

[Sherlock Holmes]

Interestingly when i framed my table in this format i got my desired result

[Sherlock Holmes]

Yes i am sharing my spreadsheet of the previous case

[Sherlock Holmes]

Its interesting isnt it ? Even in the vlookup tutorial the explanation was done on a table of this format and hence i tried to do it that way. Could there be a specific reason as to why this is happening?

[Anand Iyer]

I don't think I received the sheet yet...Have you shared it, and sent the link?

--

Cheers,

Anand

[Sherlock Holmes]

i have attached a screenshot to my message , is it not visible?

[Anand Iyer]

Ok, no need to do it now.

If you look at the picture you shared, you've used 3 as the 3rd parameter.  third column starting with Category is Price.

--

Cheers,

Anand

[Sherlock Holmes]

Yes i have tried experimenting with many values of range and parameter. What i have observed is that vlookup conducts a search horizontally across the first row  and hence the parameter entered was  returning price, when i changed the order of my table and used category as the first row with the required parameter it returned the desired category.

[Sherlock Holmes]

It is however contradicting the definition of vlookup , but practically this is what i am observing

[Anand Iyer]

well, I'm slightly confused with what you're saying, to be honest.

I think it's best to leave it to support now:)

Watson signing off...

--

Cheers,

Anand

[Sherlock Holmes]

Well i guess its not elementary , my dear Watson.

[Sherlock Holmes]

Support team please help us!!!

[Jai Hanani]

VLOOKUP : Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.
Google Sheets Documentation for VLOOKUP

[Sherlock Holmes]

Yes sir, i too expected that due to its definition, but if you refer to the screenshots i have sent earlier, it is observable that it is returning a column name rather than a value so that is when i decided to change the orientation of my table and wrote the same formula , i got my result. i could conclude that vlookup is providing correct answers only when the entire table is rotated by 90 degree anticlockwise from its usual format . Meaning that it was definitely conducting a search across a row .