Vlookup formula not working

Erica Esson 4

I need some help with a VLOOKUP formula in excel. I’m trying to get consIDs from excel sheet2 that matches email addresses from sheet1. Is this formula correct? =VLOOKUP(J2, Sheet2!$K$1:$K$74,Sheet2!A:A,TRUE)

 

Denise Covington

I always use FALSE so I get an exact match.

Joe Lindell

Where you have Sheet2!A:A, that should be the number of columns, starting with the email address on sheet2 to the column with the Constituent ID on sheet2. 


So, if the email address was in column A and the email address was in column B, it might look something like this:


=VLOOKUP($A2,Sheet2!$A$1:$B$38,2,FALSE)


[Replacing $B$38 with the bottom-right cell of sheet2]

Erica Esson 4

It doesn't work. I'll use MS Access. Thank you for your prompt responses. I really appreciate it.

Alicia Barevich

Does Excel show an apostrophe before the number for your const IDs in the text bar at the top? Does it have a green triangle in the cell? If so, it's treating it like text, and vlookup won't work. There are multiple ways to fix this, my favorite being:

Add a column next to your ID column, name it Const ID.

=A2*1

Copy that formula down for the whole column.

Highlight the column, copy, right-click, paste as values.

Delete the original ID column.


Your VLookup formula will need to be revised, since its source column was deleted. Alternatively, you could paste these fresh values in your original column, but you still may need to check your vlookup formula.

Alan French

Alicia Barevich‍ there's a quicker way that I discovered recently, in case you're interested: highlight the column of ID numbers and use Excel's text-to-columns tool on the Data section of the ribbon menu. Choose 'delimited' and click Finish and you're done! It shouldn't matter which delimiting character you choose, since the column's only got numbers in it.