James has attended:
Excel Advanced - Formulas & Functions course
Excel VBA Intro Intermediate course
VLOOKUP working for some but not all
Hi,
I have VBA code which puts in the formula below:
=IF(AF10="","",IF(VLOOKUP(L10,'Lookup Tables.xlsx'!EoS_Pools,2)="Unaffected","",VLOOKUP(L10,'Lookup Tables.xlsx'!EoS_Pools,2)))
The range EoS_Pools contains reference numbers and then applicable regulations to be pulled back to a record, copy below:
J00001 Pool 10
J01878 Unaffected
K00001 Pool 10
K01878 Unaffected
For the codes beginning J data is pulled back so anything below J01878 and above J00001 returns Pool 10, for example J01581, however this does not work on the K codes which all return a blank value?
The named range includes both codes and the format of cells being looked up is correct? Do you have any idea on why K would not return same as J?? I am at a loss.
Thanks in advance,
RE: VLOOKUP working for some but not all
I managed to find the reason for this error. My listing contained codes with other letters at the beginning. As I had not put the K codes in alphabetical order the lookup was not pulling back what I expected. I have adjusted the ordering and the lookup now works fine.
Thanks,
James
