• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLookUp

G

GN0001

Member
I have a column:

Red

Purple

Orange

Red

Green

Yellow

Pink

Yellow


I enter this VLookup function. When It searches the search value, it gives me either #NA

or it returns Red for all the values. What is the problem?

VLOOKUP("Red",$B$1:$B$9,1,FALSE)


Thank you for the help.

Guity
 
What is the goal? Your formula currently states to find the word "Red" within a 1 column table (which you already know is there) and return the corresponding value from that column (which...is "red"). Copying this formula somewhere else will make no change.


I'd recommend either reading the XL help file, Chandoo's guide to VLOOKUP, or giving us more detail as to what you're trying to accomplish.
 
This is a small sample of what I am doing. If I have 1000 rows of different colors in column B and only I want to pickup the red values and show them in column G, what should I do? Why this vLookup doesn't return the correct answer?

Regards,

Guity
 
Hi Guilty,


Do you want to show something other than the word "Red", what is next to Red that you want and where is it?, do you want the address of where Red appears?


Because all you are going to get is the word Red.
 
I only want to pick up text red. In my column I have every color and I Only want to pick up the red. I mean if the color is yellow, it needs to bring back #N/A, since the value I am searching is red. It needs to give the result back when the text is red.

Regards,

Guity
 
Hi Guilty,


Still not absolutely sure what you are trying to achieve, but if I understand you correctly you can try:


In column G you could enter


=IF(B1="Red","Red","#N/A")


This will give you all cells in B that are = "Red"


You could also use Autofilter and filter on Red
 
Kchiba,

Yes, thank you, they are good solutions. I want to know why this is not working:

I want to say if the search value is equal to red, I want to pull off only the red value with VLookUp. The solutions you gave me are solving my problem. But I want to know why this is not working:


This is my column:

Red

Purple

Orange

Red

Green

Yellow

Pink

Yellow


VLOOKUP("Red",$B$1:$B$9,1,FALSE)


Regards,

Guity
 
Guity

Vlookup as your using it will only return the first occurrence of the word

I prefer to use sorted lists when using VLookup although not necessary in all cases it is preferable.


Have a read through Chandoo's week of VLookup for more uses of VLookup

http://chandoo.org/wp/2010/11/01/vlookup-excel-formula/
 
Basic structure:

http://chandoo.org/excel-formulas/vlookup.html


More in-depth:

http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/


Chandoo's guide to 51 formulas:

http://chandoo.org/excel-formulas/


kchiba's formula is probably the best. The only change I would recommend would be to use a cell reference so that you can change what color you want to find, like so:

=IF(B1=$C$2,B1,NA())


Where C2 contains the color you want to find.
 
You must log in or register to reply here.
Back
Top