VLOOKUP working for some but not all

Resolved · Medium Priority · Version 2010

Thu 7 Aug 2014, 11:39 Delegate James said...

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,

Thu 7 Aug 2014, 13:17 Delegate James said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Difference between Two Dates

The DATEDIF function computes the difference between two dates. The DATEDIF function is as follows:

=DATEDIF (Date1,Date2,Interval)

Please note that Date1 must be less than (earlier) or equal to Date2

Please note that Interval must be one of the following codes: "d" (in days), "m" (in months), "y" (in years) expressed in quotes

View all Excel hints and tips