VLOOKUP or INDEX/MATCH doesn't work

0
votes

I'd like to look up data from 2 table if it exists in on another. I used vlookup but didn't work, and INDEX MATCH as well. I dunno why exactly the root cause is. here is the table :

a) Data Source

  Data Source         Exist in Table Array Sheet?
  **40000**             #N/A
    40300   
    40400   
    40401   
    40402   
    40500   
    40600   
    40700   
    40701   
    40702   
    40897   
    40898   
    40899   
    40999   
    41000   
    42000   
    42100   
    42120   
    42150   
    43000   
    43012   
    43020   
    43099   
    45000   
    45005   
    45800   
    45900   
    45905   
    46000

b) Table Array

20100
20300
24060
21998
21953
21950
21952
29000
28000
40000
40500
45900
45000
61000
62909
66000
66100
66900
66200
62901

c) Here's the vlookup or INDEX MATCH function which i've tried : 

=INDEX('Data Source'!E6:E73,MATCH('Data Source'!D5,Array!E6:E73,0))

or

=VLOOKUP(D28,Array!E28:E95,1,FALSE)

I dunno why it won't lookup.

I have uploaded the excel file on : https://www.dropbox.com/s/j9c9y2hm7avcbyz/Book1.xlsx?dl=0

1
excelindexingexcel-formulavlookuplookup-tables

1 Answers

0
votes

The pseudo-numbers in the Array worksheet are actually text-that-look-like-numbers while the numbers in the Data Source worksheet are actual numbers that someone intentionally left-aligned (numbers are right aligned by default). Select Array!E6:E73 and run Data ► Text to Columns ► Fixed Width ► Finish to convert the pseudo-numbers to actual numbers. True numbers will never match with text-that-look-like-numbers.

Go to the Data Source worksheet and repeat the process by first selecting 'Data Source'!D5:D450 and then repeating the Text to Columns command. There are rogue text values interspersed with the actual numbers (e.g. 41000).

After that, change the formula in 'Data Source'!E5 to this,

=VLOOKUP('Data Source'!D5,Array!E$6:E$73,1, FALSE)