Using VLOOKUP to pull info from another Worksheet - Not working!

[a2zriddler]

Hi there,

I've input this formula to take data from a cell on one worksheet, and pull it into another cell on another worksheet.

=VLOOKUP(R3,'[uk_beauty_browse_tree_guide._TTH_.xls]BEAUTY PRODUCTS (11.2.2016)'!$A:$B,1,FALSE)

R3="Pedicure"
The other worksheet Table_Array of Column A & B
Return the value of the 1st column (Column A)

I keep getting the response #N/A which is incorrect as the word Pedicure (R3) is definitely there and the data I want returned is definitely in the cell next to it.

I need to figure this out and apply the formula to a whole column to quickly find the word in found in R3,R4,R5 and so on, and find it in the other worksheet to return the value of the cell next to it.

Any help on this would be greatly appreciated

 

[Jonmo1]

If you're wanting to return the data from the cell next to Pedicure, you'd need to change the 1 to a 2.
But that's not the cause of #N/A

#N/A pretty much means the value of R3 does not exist 'Exactly' in Column A of the other book/sheet.
Check your cells for exact spelling, and extra spaces like "Pedicure " or " Pedicure"

 

[Jonmo1]

The other worksheet Table_Array of Column A & B
Return the value of the 1st column (Column A)

Wait, are you saing Pedicure exists in column B of the other sheet, and you want to return the value next to it in column A?
Vlookup can't do that, it's Zoolander Challenged.
It can only look 'To the right'

It finds the matching value in column A, and returns a value from a column 'To the right' of where the match is found.

Try
=INDEX('[uk_beauty_browse_tree_guide._TTH_.xls]BEAUTY PRODUCTS (11.2.2016)'!$A:$A,MATCH(R3,'[uk_beauty_browse_tree_guide._TTH_.xls]BEAUTY PRODUCTS (11.2.2016)'!$B:$B,0))

 

[Legacy 373233]

Hi, and welcome to the board.

The main error that i see with your formula is with the third argument (the column selection). It should be changed to 2 or greater.

That's because Vlookup only works from left to right, looks the value you're asking for in the rows of the first column, and then, it moves N columns to the right to return a valid value.

Hope that helps.

 

[a2zriddler]

Thank you so much your reply

Sorry if I explained poorly.

I have one spreadsheet with the word Pedicure in cell R3 and then Nail Clippers in R4 and so on.

I want to search (VLOOKUP) those words in another worksheet and return the value next to the cell in which that word is found. e.g. Cell B238 = Professional Pedicure Bowl 8" and Cell A238 = 17056489
I want it to find Cell B238 and Return the number in A238.

I can't attach screens which would be a lot easier

Hope this explains it better

 

[Jonmo1]

Ok, that makes it even more complicated because the Cell B238 does not match EXACTLY with R3

Try
=INDEX('[uk_beauty_browse_tree_guide._TTH_.xls]BEAUTY PRODUCTS (11.2.2016)'!$A:$A,MATCH("*"&R3&"*",'[uk_beauty_browse_tree_guide._TTH_.xls]BEAUTY PRODUCTS (11.2.2016)'!$B:$B,0))

 

[Legacy 373233]

Yeah, that's a better explanation.


Everything seems ok, but i haven't used Vlookup to retrieve info using parts of a string, i guess it only works with the full exact string, (by exact, i mean the same string)


you might need to try using another word in order to use Vlookup.

Hope somebody have another point of view

 

[Jonmo1]

but i haven't used Vlookup to retrieve info using parts of a string

You can use wildcards in Vlookup, like
=VLOOKUP("*some word*",A:B,2,0)

That will find a match to say "this is some word my friend" in column A


But the bigger problem here with vlookup, is that it can't go backwards (find match in column B and return value from column A)
That's why we need Index and Match.

 

[a2zriddler]

Thanks again mate.

You're right it does not exactly match. The cell that I want to find in this example contains "Beauty/Nail Care/Tools & Accessories/Manicure & Pedicure Sets".

Good shout on the Wildcard option, I thought that was the ticket with it being aproximate, but that has just given me red error brackets for the MATCH function.

It shows like this:
=INDEX('[uk_beauty_browse_tree_guide._TTH_.xls]BEAUTY PRODUCTS (11.2.2016)'!$A:$A,MATCH("*"&R3&"*",'[uk_beauty_browse_tree_guide._TTH_.xls]BEAUTY PRODUCTS (11.2.2016)'!$B:$B,0))

And gives the dreaded "Check the cell references" pop up followed by the cursed "Bl-Bong" which only asserts my shortcomings!

I'm baffled :S

 

[a2zriddler]

Ok Jorge,

Im not a pro with Excel and have taught myself and managed to get by with just using the easier functions (IF, COUNTIF, VLOOKUP, CONCATENATE etc).

You're input was very worthwhile as it allowed me to eliminate your solution and narrow my train of thought!

Thanks alot mate