Simple VLookup Formula having Issues

Hello, I included a sample to explain my problem.

So I have been using VLOOKUP's a bunch the last month or so, and am completely stumped at why this isn't working.

It seems really simple, In Sheet2, cell B2 I create a VLOOKUP formula, but it is not working.

Using cells A:A from 'sheet2' in the first 1st part of formula.

Using cells A:C from "sheet1" in the 2nd part of formula.

and 3,0 as 3rd/4th part of formula so it should match with a number. I also know the names match up on Column A of sheets 1+2.

Thanks for the help in advance, I wish I didn't have to waste a thread on this but I've wasted so much time trying to figure it out when I have 7 other working VLOOKUP's in the same dang sheet..

self deleted- My apologies, I was looking at the wrong file

the syntax for a vlookup is...

=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

you have...
=VLOOKUP(A:A,Sheet1!A:C,3,0)

the "what-you-want-to-find" needs to be a single cell address, not a range

Again, Sorry, I deleted my post, I was looking at the wrong file

hmm ok, I have never seen vlookup() used in that manner and it does seem to work. the problem is that that the names on sheet2 dont exist on sheet1.

When I copied "Miguel Cabrera" (sheet2 A1), onto sheet2 (anywhere) it found it

Originally Posted by FDibbins

hmm ok, I have never seen vlookup() used in that manner and it does seem to work. the problem is that that the names on sheet2 dont exist on sheet1.

When I copied "Miguel Cabrera" (sheet2 A1), onto sheet2 (anywhere) it found it

"Sheet2" has a bigger database than "Sheet1" so most people in sheet2 are definetly in sheet1. You used Miguel Cabrera as an example (A2), he is in both sheets.

So, my exact question would now become this:

A2 of Sheet 2 is "Miguel Cabrera". He is also in "Sheet1" I want to pull his cells from sheet 1 over to sheet 2 in cell B2 (Next to his name "Miguel Cabrera in A2)

hi jordan1214, you seem to have trailing spaces in the names of Sheet1. so either clean them up using the TRIM formula somewhere & paste over the cells or use this array formula:
=VLOOKUP(A2,TRIM(Sheet1!$A$1:$C$500),3,0)
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

i usually don't like to range up the whole column. slows down Excel. so just put in more than you would realistically reach

The problem is you have trailing spaces after names, so an exact match does not work, this:
=VLOOKUP(A:A&"*",Sheet1!$A:$C,3,0)
gets rid of most of the errors, but not all,
I would be looking at ways to reconcile the leading/trailing spaces in the data, before the vlookup, but thats just me..

Yes I can see what you are trying to do, but i say again, the names on sheet2 do NOT match those on sheet1 (and "Miguel Cabrera" is certainly not in there - give me the row number if he is?...or maybe we are looking in different files lol, I am using sample11 from post1?)

I used this to just check if the names on sheet2 are on sheet1, it didnt find any matches
=MATCH(A2,Sheet1!A:A,0)

aahh yes, i see it now. search functions like vlookup and match are VERY specific - 1 mis=spelling or extra (or missing) space, and it wont find what you are looking for. I should have considered that, sorry

If you cant get rid of the trailing spaces, try using this...

=VLOOKUP(A:A&"*",Sheet1!A:C,3,0) (there will still be some names not on sheet1, from what I can see)

edit: OK I need to look above to see who else has posted what else lol...apologies to dred and beni, i didnt notice you have pretty much already suggested what i did

@ FDibbins, he is there, just has a trailing space, so any exact match without a wildcard will not work...
my adaptation does work, but I would amend it to this:
=IFERROR(VLOOKUP(A:A&"*",Sheet1!$A:$C,3,0),"")
then if any of the blank returns SHOULD have a value, you need to look closer at the data to see why it DOESN'T return a value

@ FDibbins - N0 worry , we got the solution for OP, so all is good

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

while it seems like there's no impact on using the whole column for a lookup_value, there's no reason to do that. so maybe if you want to go with dredwolf's suggestion, go for:
=IFERROR(VLOOKUP(A2&"*",Sheet1!$A:$C,3,0),"")

i would still go for TRIM instead because wildcards looks for entries that contain the name. maybe unlikely, but if you just this name in Sheet2
Lily Alleness

and 2 of these in Sheet 1. they will return you the same results
Lily Allen
Lily Alleness

Originally Posted by jordan1214

Can you advise me where to reach up on undoing of the "ranging up the whole column". I can deal with it being a little slow, but if I only have to spend some time and can have the same results with excel being quicker, then great.

i don't have an exact number. the lesser the better. some people have 10,000 rows of data. you can't ask them to go lesser than that. but ranging up say 15,000 rows is better than 1,000,000 over rows. but if you do have such huge data, do consider using dynamic ranges too. google for Excel Table, OFFSET or INDEX to do that.

From what I could seem there were indeed names on sheet2 that did not appear on sheet1 (I used find/replace to search on names like "Ian Kinsler" - searched for "kin" and couldnt find that name)

The trim() function won't really speed it up, the point was using whole column ranges will slow it down.., limit the range to 1.5 - 2 x the current range range if you do not expect major number differences in amount of data, if the the data is expected to grow lots, go maybe 10x size of current range, if it is completely unpredictable, maybe go for dynamic named ranges...
At least, that is my take on what benishiryo is trying to tell you, the TRIM() just get's rid of the excess leading and trailing spaces (which may be a better way to go, especially if this is prevalent in your data),
but the limiting of the range sizes will definitely help improve performance, 500 calculations compared to 1million + per a column and/or cell

@dredwolf:
spot on. that's what i was trying to get across
=)

@jordan1214:
in fact, using my TRIM formula as an array would slow the workbook. but it's for accuracy.