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]

What?
Easier functions? If Countif Vlookup Concatenate ??

The index/match formula works just fine for me (copied exactly as I posted it)

 

[a2zriddler]

Sorry Jonmo,

I was replying to Jorge just explaining my Excel knowledge.

So Jonmo.. if VLOOKUP cannot go backwards, surely I can simply swap Column A's data with Column B's and that should allow me to keep your first formula suggestion as the data will be in the position to "go forwards"??

Would that work in theory?

Thanks again for this help mate, it is not only helping but educating me

 

[Jonmo1]

Would that work in theory?

Yes, not just in theory, but in reality as well.

 

[a2zriddler]

Hi Jonmo,

I swapped the columns round in the other worksheet and had a fiddle with the formula and the closest I've got is using these 2:

=VLOOKUP(R3,[uk.eu_browse_tree_mappings._TTH_.xls]MAPPINGS!$A:$B,2,TRUE)

=VLOOKUP("Pedicure",[uk.eu_browse_tree_mappings._TTH_.xls]MAPPINGS!$A:$B,2,TRUE) - I know that using the actual "word" doesn't help me in the long run but i'm just trying to play around to narrow down the options.

This appears to have got rid of the #N/A error, and does return the right type of data, but for some reason it doesn't return the data from correct cell.

It returns the data from the cell at the very bottom of the range I selected!

So I'm very close but just can't figure out why it is returning the data from that cell.

So in summary, It's almost as if it is neglecting the lookup_value and just defaulting to the last cell in the column of the table array!

But I am very close as it's returning the correct type of data from the correct column, just from the wrong row as the lookup seems to not be working.

Thanks again for your help mate.. Absolute legend!

 

[Jonmo1]

Try changing R3 to "*"&R3&"*"

And change TRUE to FALSE