I'm trying to create a VLOOKUP in the CustomerID column of the Orders tab based on the ID column of the Customers tab using the CustomerName column as the foreign key identifier. I would post the excel file here for reference but I don't see an option. Images below:
The data types are the same for CustomerName across excel tabs and I saved the file as excel instead of csv. Also confirmed there are no trailing spaces in the CustomerName columns.
The key column for VLOOKUP should be the leftmost column. Then it should be sorted ascending.
So you need to move CustomerName to left and sort the range ascending by CustomerName. Also (if you use or need to use ID as a key anywhere else) you can duplicate the ID column to right from CustomerName (e. g. in F: [F1]=A1 fill down) and refer $B$1:$F$xxx in VLOOKUP.
How have you written your formula ? Because it seems to me that VLOOKUP requires the field where you actually look to be situated to the right of the reference field (here in the cusztomer database) : Excel looks for the name and take the correspondance in another column to the right. Maybe copy/paste the customer ID in column D and do the Vlookup from columns B to D, looking at column 5 as your third argument
The VLOOKUP function requires that the Lookup Column be the leftmost column (just as the HLOOKUP function requires the Lookup Row to be the Topmost Row)
However, a VLOOKUP is just a simplified way to do an Index/Match lookup, without needing to mess around with the the INDEX and MATCH functions
We want to find the item at the nth Index of the ID column (Customers!$A:$A), where n is the row where the Name (Customers!$B:$B) is an exact Match for the input value ($C2)
So, that will be =INDEX(Customer!$A:$A, MATCH($C2, Customers!$B:$B, 0))
