Issue with the VLOOKUP - wrong find

[nLike]

Hi,

In the table below I'm having trouble getting vlookup function to work properly. I need to compare two tables (each having 1st column the same, but the values in the further columns differ). The order of the names (sql tables) sometimes is different, and I'm not able to use the sort function because of some reasons, I need to use the vlookup function to compare the values from the first table versus the values in the second table.

So, my idea was to have a third instance of the table that would compare the difference between the first table and the second and give the result. I wanted this to achieve with VLOOKUP.

However, I came up to an interesting "bug" in the vlookup function here. For some reason VLOOKUP function only works when I search up to 30 rows deep, but if I change it to 31 it automatically gives the wrong value.

So down below is the 35 row excerpt table that will do the trick for me to explain. Say, this is going from A1 to G35. For testing purposes, next to it is the same copy pasted table in the fields, going from J1 to P35.

Now in, f/ex field R:1, I enter function =VLOOKUP(A1,J1:P30,1) I get the value LEP_HIS.ArchiveStatus (which is correct). But if I do =VLOOKUP(A1,J1:P31,1) I get the Value COM_OGC.LDAT_Cliplist (which is obviously wrong).

I have no idea why is this happening. Is this some sort of a string conversion thing that's working behind in excel that's causing the issues? Do you think there is some workaround?

Thanks!

P.S. I know upload Excel file would be easier, but I didn't find the option to upload file, I guess it's forbidden.

LEP_HIS.ArchiveStatus	33122	12184		8032	4040	112			LEP_HIS.ArchiveStatus	20	12184		8032	4040	112
LEP_HIS.PRT_PrintJob	1133	9432		9048	128	256			LEP_HIS.PRT_PrintJob	1133	9432		9048	128	256
LEP_HIS.RFA_LDAT_sbl	37696	7824		5928	1800	96			LEP_HIS.RFA_LDAT_sbl	37696	7824		5928	1800	96
COM_SAP.ZCREA_Modul	19065	6928		3832	2632	464			COM_SAP.ZCREA_Modul	19065	6928		3832	2632	464
LEP_SYS.Modullist	19003	5200		2984	1944	272			LEP_SYS.Modullist	19003	5200		2984	1944	272
TRK_HIS.TrackLog	5341	4680		4616	8	56			TRK_HIS.TrackLog	5341	4680		4616	8	56
LEP_HIS.***_LDat_PIC	12328	4176		2864	624	688			LEP_HIS.***_LDat_PIC	12328	4176		2864	624	688
LEP_HIS.BoardStatus	1640	2808		648	760	1400			LEP_HIS.BoardStatus	1640	2808		648	760	1400
LEP_HIS.OGC_LDAT_Cliplist	10237	2512		1064	776	672			LEP_HIS.OGC_LDAT_Cliplist	10237	2512		1064	776	672
LEP_HIS.EventLog	9091	2184		2056	32	96			LEP_HIS.EventLog	9091	2184		2056	32	96
LEP_BAS.Templates_ZCREA_Modul	16262	2128		1176	880	72			LEP_BAS.Templates_ZCREA_Modul	16262	2128		1176	880	72
COM_LEP.COM_Log	2959	1352		984	64	304			COM_LEP.COM_Log	2959	1352		984	64	304
LEP_BAS.OGC_LD	3586	1288		696	208	384			LEP_BAS.OGC_LD	3586	1288		696	208	384
LEP_HIS.WorkTimes	2749	1240		728	408	104			LEP_HIS.WorkTimes	2749	1240		728	408	104
LEP_SYS.GenerationStatus	2807	1240		528	584	128			LEP_SYS.GenerationStatus	2807	1240		528	584	128
LEP_BAS.FOA_LD	3703	656		416	216	24			LEP_BAS.FOA_LD	3703	656		416	216	24
LEP_BAS.PIC_Config	1245	624		456	104	64			LEP_BAS.PIC_Config	1245	624		456	104	64
LEP_HIS.***_Log	562	592		168	224	200			LEP_HIS.***_Log	562	592		168	224	200
LEP_HIS.SAP_StatusRequest	1315	592		360	136	96			LEP_HIS.SAP_StatusRequest	1315	592		360	136	96
COM_OGC.LDAT_Cliplist	2538	520		320	16	184			COM_OGC.LDAT_Cliplist	2538	520		320	16	184
LEP_HIS.ErrorLog	1093	456		344	16	96			LEP_HIS.ErrorLog	1093	456		344	16	96
TRK_HIS.ETE-OperationLog	321	392		344	8	40			TRK_HIS.ETE-OperationLog	321	392		344	8	40
COM_***.LDat_PIC	923	392		248	16	128			COM_***.LDat_PIC	923	392		248	16	128
TRK_HIS.***-OperationLog	476	392		360	8	24			TRK_HIS.***-OperationLog	476	392		360	8	24
LEP_HIS.OGC_Error	1386	384		144	96	144			LEP_HIS.OGC_Error	1386	384		144	96	144
LEP_HIS.KSK_position	672	336		96	168	72			LEP_HIS.KSK_position	672	336		96	168	72
LEP_BAS.ETE_LD	1024	328		152	88	88			LEP_BAS.ETE_LD	1024	328		152	88	88
LEP_HIS.USW_Error	145	256		48	56	152			LEP_HIS.USW_Error	145	256		48	56	152
LEP_HIS.ETE_Log	155	240		88	120	32			LEP_HIS.ETE_Log	155	240		88	120	32
LEP_HIS.RFA_ADat_prt	259	232		128	48	56			LEP_HIS.RFA_ADat_prt	259	232		128	48	56
COM_SAP.ZCREA	402	224		104	96	24			MUDA	402	224		104	96	24
LEP_HIS.RFA_Log	389	224		64	104	56			LEP_HIS.RFA_Log	389	224		64	104	56
TRK_HIS.FOA-OperationLog	435	200		136	8	56			TRK_HIS.FOA-OperationLog	435	200		136	8	56
TRK_HIS.TrackErrorLog	0	200		168	8	24			TRK_HIS.TrackErrorLog	0	200		168	8	24
TRK_HIS.RFA-OperationLog	598	200		176	8	16			TRK_HIS.RFA-OperationLog	598	200		176	8	16

<tbody>
</tbody><colgroup><col><col span="2"><col><col span="3"><col span="2"><col><col span="2"><col><col span="3"></colgroup>

 

[hiker95]

.S. I know upload Excel file would be easier, but I didn't find the option to upload file, I guess it's forbidden.

nLike,

Welcome to the MrExcel forum.


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[mrhstn]

For reference the default lookup in VLOOKUP is TRUE (1), which will find an approximate match, as your table isn't sorted alphabetically this will throw the results you mentioned.

Use =VLOOKUP(A1,J1:P35,1, 0) or =VLOOKUP(A1,J1:P35,1, FALSE) this will bring the result you want as it'll look for an exact match.

 

[nLike]

Hi,
@mrhstn Thanks, that did the trick! Didn't figure out you need to use that last parameter.
@hiker95 will sure look at that going on! Thanks for the welcome.

 

[mrhstn]

You're welcome.

FYI. If you can always have your data sorted then use the TRUE parameter, the performance difference is quite astonishing.

MrExcel Podcast 2031 - https://www.youtube.com/watch?v=Bn-3ejknUGY

 

[hiker95]

@hiker95 will sure look at that going on! Thanks for the welcome.

nLike,

Thanks for the feedback.

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 11/29/2017

https://www.mrexcel.com/forum/excel...asting-specific-cells-sheet2.html#post4961995