Vlookup Returns Wrong/No Data

[TomCat]

My Data Range is (shortened for this example):
124 00A2RG000024CMNM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1
125 00A2RG000024CLTM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1
126 00A2RG000025CDGM A2RG.CS.1.1.1 A2RG.CS.1.1.1.2
129 00A2RG000026LDGM A2RG.CS.0.0.0 A2RG.CS.1.1.1.3
135 00A2RG000031ICMM A2RG.CS.1.1.1 A2RG.CS.1.1.1.8
137 00A2RG000032CLTM A2RG.CS.1.2.1 A2RG.CS.1.2.1.1

My first formula in line 26 below is: =VLOOKUP(E26,$D$124:$F$149,2), and
respectively E27 and E28

My results are:
26 #N/A #N/A 00A2RG000024CLTM
27 A2RG.CS.1.1.1 A2RG.CS.1.1.1.8 00A2RG000032CLTM
28 A2RG.CS.0.0.0 A2RG.CS.1.1.1.3 00A2RG000026LDGM

As you can see, Line 26 show #N/A, line 27 displays the data from 2 lines
above, and line 28 is correct.

This is really throwing us off! Can you help? Thanks....TomCat

 

[Niek Otten]

Make your formula

=VLOOKUP(E26,$D$124:$F$149,2,FALSE)

Look in HELP for the meaning of the 4th argument

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| My Data Range is (shortened for this example):
| 124 00A2RG000024CMNM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1
| 125 00A2RG000024CLTM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1
| 126 00A2RG000025CDGM A2RG.CS.1.1.1 A2RG.CS.1.1.1.2
| 129 00A2RG000026LDGM A2RG.CS.0.0.0 A2RG.CS.1.1.1.3
| 135 00A2RG000031ICMM A2RG.CS.1.1.1 A2RG.CS.1.1.1.8
| 137 00A2RG000032CLTM A2RG.CS.1.2.1 A2RG.CS.1.2.1.1
|
| My first formula in line 26 below is: =VLOOKUP(E26,$D$124:$F$149,2), and
| respectively E27 and E28
|
| My results are:
| 26 #N/A #N/A 00A2RG000024CLTM
| 27 A2RG.CS.1.1.1 A2RG.CS.1.1.1.8 00A2RG000032CLTM
| 28 A2RG.CS.0.0.0 A2RG.CS.1.1.1.3 00A2RG000026LDGM
|
| As you can see, Line 26 show #N/A, line 27 displays the data from 2 lines
| above, and line 28 is correct.
|
| This is really throwing us off! Can you help? Thanks....TomCat
|

 

[Chip Pearson]

You need to include the 4th parameter to VLOOKUP. If this parameter is
FALSE, then VLOOKUP doesn't require a sorted data range and will return a
value when (and only when) an exact match is found. If this parameter is
omitted or TRUE, VLOOKUP requires that the data range be in sorted order and
will return a closest match if an exact match is not found.

=VLOOKUP(E26,$D$124:$F$149,2,FALSE)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[TomCat]

Y'all are great! Thanks!

TomCat

You need to include the 4th parameter to VLOOKUP. If this parameter is
FALSE, then VLOOKUP doesn't require a sorted data range and will return a
value when (and only when) an exact match is found. If this parameter is
omitted or TRUE, VLOOKUP requires that the data range be in sorted order and
will return a closest match if an exact match is not found.

=VLOOKUP(E26,$D$124:$F$149,2,FALSE)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)