Stephen has attended:
Excel Intermediate course
Excel Advanced course
Excel VLOOKUP mix of text and numbers
Hi , have an issue with a VLOOKUP .
Data contains a mix of numbers and text , 00 , 01 , AA etc
The VLOOKUP works on a mix of number / text , but not pure numbers !? This is after formating cells (Convert to Number)
Any suggestions appreciated , eg below
Source VLOOKUP
60 #N/A
70 #N/A
CN CN
DY DY
E9 E9
Basic Formula = VLOOKUP(A:A,C:C,1,0)
tks
RE: Excel VLOOKUP mix of text and numbers
Hi Stephen,
Thank you for the forum question.
Try to use the exact function. =Exact(A10,C10) where the first cell reference is a cell in column A where you have 60 and the second cell reference is a cell in column C where you also have 60. The function will return False because Excel will not see the content of the two cells being the same.
You may have some spaces in one of the columns or some hidden characters.
If this is the reason try =VLOOKUP(Clean(Trim(A:A)),C:C,1,0)
Please let me know if this is doing the job.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Excel VLOOKUP mix of text and numbers
;o) .......many tks for prompt response , that fixed the issue .
One further Q please guys .... I need to use INDEX / MATCH I think .
So I need to compare 2 columns of data across 2 tabs , then return the value in the adjacent column
Tab 1 (ref data) in column A
AA
AB
AC
AD
AE
Tab 2 , Match the letters in column A , and return the value in column B
AA CAD
AB CAD
AC EUR
AD GBP
AE USD
Tks again guys
SF
RE: Excel VLOOKUP mix of text and numbers
Hi Stephen,
Index + Match versus Vlookup.
You can do it with a Vlookup but Index + Match is a much better solution. Worst case the Index + Match is faster for Excel to handle. Best case Index + Match is extremely faster and use less of your pc's memory.
The structure:
Tab1!B1=Index(Tab2!$B$1:&B$4,Match(Tab1!A1,Tab2!$A$1:$A$4,0))
The Index function needs first the range, where you have the data you will like to return to the select cell. The Index function then needs the row number. The Match function returns the row number to the Index function
I hope this make sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Excel VLOOKUP mix of text and numbers
Tks Jens ..... this worked beautifully !!
One little glitch was on the 'F4' shortcut ..... it didn't auto-default the $ part of the formula for some reason ??
so the $B$1:&B$4 ?
Hard to hard key it ..... did I miss a key-stroke or action do you think ?
tks again
RE: Excel VLOOKUP mix of text and numbers
Hi Stephen,
I guess you are using a laptop.
You will have to press FN F4 or change your laptop to use the F keys.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
