Help! V Lookup not working

New Contributor

Hi All,

It's been a while since I have used VLookup. I cannot get the formula to work in this instance and it's driving me crazy! I'm getting a #REF error and I can't figure out why.


Does this look correct to you?



I am looking to match emails in 2 sheets in the same workbook, and then return the value n the 2nd column on the 2nd sheet.

3 Replies
The problem is you are requesting the 2nd column of the reference Sheet1!C:C which is only 1 column. Try C:D like this:

@mtarler Thank you! I keep getting N/A errors.. do numbers in a column for a VLookup need to be formatted in a specific way? I changed the formula to =VLOOKUP(F2,Sheet1!A:C,1,FALSE) and have tried the entire table array of A:E and I still can't get the formula to work. I am trying to bring over the values in column B.. the CaseSafe ID.


Screen Shot 2022-04-12 at 9.11.31 AM.png



It looks like you want to MATCH column 3 (email) and RETURN column 2 (CaseSafeID)?
You can't use Vlookup for that because Vlookup will Lookup on the 1st column and then return the nth column.
If you have Excel 365 I recommend you use XLOOKUP
=XLOOKUP(F2,Sheet1!C:C,Sheet1!B:B,"not found",0)
that said I also recommend you format that sheet/data as a "Table" (Home -> Styles -> Format as Table)
Then use:
=XLOOKUP(F2,Table1[Email],Table1[CaseSafeID],"not found",0)
This way excel isn't looking over ten of thousands of blank lines for your value but only looking at the data in the table.
ALSO, assuming you want this for more than just F2 you can use dynamic array so:
=XLOOKUP(F2:F100,Table1[Email],Table1[CaseSafeID],"not found",0)
to do the lookup on all the values from F2 to F100...
Lastly, IF you do NOT have 365 then you need to use INDEX(MATCH())