Why doesn't vlookup find the match?

Jorge E. Jaramillo

unread,

Jun 12, 2009, 5:54:01 PM6/12/09

to

I have a column with some 200 values and a large table with 4 columns in
which I need to find the valuesof the column. When I try to find matches
using =VLOOKUP(E2,A2:C2808,3,FALSE), sometimes it finds the matches and
sometimes it doesn't. I am positive that some of the values that the function
doesn't find, are exactly the same as ones on the table.

Is there any way to fix this and make it work properly? If I remove false
and change it for true or leave it blank, it produces results that can not be
trusted

Thank

Jorge E Jaramillo

Ken Wright

unread,

Jun 12, 2009, 6:39:45 PM6/12/09

to

If the ranges are right and it doesn't find it, it doesn't match, period.

Connect the two cells with a simple formula, eg =A3=E7 and see if you get a
TRUE or FALSE. I'll bet on FALSE.

Usual culprits are numbers stored as text, or vice versa - look identical,
but they are not.

If it works sometimes but not others then it sounds like you need to do some
data cleansing.

Regards
Ken..............................

"Jorge E. Jaramillo" <JorgeEJ...@discussions.microsoft.com> wrote in
message news:5A6F26DF-5E38-4425...@microsoft.com...

Dave Peterson

unread,

Jun 12, 2009, 6:54:08 PM6/12/09

to

Just to add to Ken's response...

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

--

Dave Peterson

Jorge E. Jaramillo

unread,

Jun 15, 2009, 1:38:14 PM6/15/09

to

I checked the data and changed the format of the columns where the data ois
and the column where the value to be searched for is.

The information to be searched is a 9 figure number that identifies
customers. I changed the format to Number on all the columns containing the
information but I still get the #N/A's.

In one of the cplumns there are many of those green triangles that indicate
that the format is not correct. Could this be the culprit? Any ideas on how
to fix it?

Thanks that with the help from this group's people, I am going in the right
direction

Jorge E Jaramillo

abqhusker

unread,

Jun 15, 2009, 2:04:01 PM6/15/09

to

Could it be because the range is not absolute? Seems to me that assuming the
data is in correct format, that the A2:C2808 range needs to be absolute
$A$2:$C$2808.

Max

unread,

Jun 15, 2009, 2:37:01 PM6/15/09

to

> =VLOOKUP(E2,A2:C2808,3,FALSE)

Try these variations (with the table array locked with $ signs)
[The 3 variations are ways to make the lookup values consistent
with what's in the lookup col A2:A2808]

=VLOOKUP(E2+0,$A$2:$C$2808,3,FALSE)
the: +0 will coerce any lookup values which are text nums to real nums, w/o
impacting its numerical value
(assuming all real nums in the lookup col A2:A2808)

=VLOOKUP(E2&"",$A$2:$C$2808,3,FALSE)
the: &"" bit will make the lookup values to text nums
(assuming all text nums in the lookup col A2:A2808)

=VLOOKUP(TEXT(E2,"0000"),$A$2:$C$2808,3,FALSE)
the TEXT function will pad leading zeros (if necess.) to the lookup values,
enabling consistency in matching with the text nums in the lookup col
A2:A2808. Adjust the "0000" part to suit

voila? celebrate success, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---

Jorge E. Jaramillo

unread,

Jun 15, 2009, 7:16:09 PM6/15/09

to

Max

I did as you suggested but it brought #N/A's as results. Then I applied the
+ 0 trick to the column where the values should be checked in and IT
WORKED!!!!!!!!!

Thanks a million

Jorge Jaramillo

Max

unread,

Jun 15, 2009, 8:42:01 PM6/15/09

to

Welcome, Jorge. Do spare a moment to press the YES button in that response,
won't you?

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

---
"Jorge E. Jaramillo" wrote:

Jorge E. Jaramillo

unread,

Jun 17, 2009, 6:52:02 PM6/17/09

to

I did so since the day I answered. Just in case I clicked again today

Max

unread,

Jun 17, 2009, 8:37:01 PM6/17/09

to

Thanks, Jorge

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Jorge E. Jaramillo" wrote:

sherryb20

unread,

Jul 30, 2009, 4:42:01 PM7/30/09

to

THANK YOU THANK YOU THANK YOU

Max

unread,

Jul 30, 2009, 6:58:58 PM7/30/09

to

Welcome, glad it helped you as well

--
Max
Singapore
http://savefile.com/projects/236895

Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"sherryb20" <sher...@discussions.microsoft.com> wrote in message
news:9AC978DF-B6AA-4C00...@microsoft.com...

Holly

unread,

Nov 10, 2009, 7:14:01 AM11/10/09

to

Thank you! I often use Vlookup to search for prices against product codes,
and am often frustrated by the formats being wrong, and not being able to
change them.

I use the method of converting to number by putting a number 1 into a spare
cell, copying that cell, then using 'paste special' - 'multiply' across the
data to convert it all to number. Which is useful some of the time.

However, as some product codes have leading zeros, these answers from Max
have given me new ways to deal with these problems.

The remaining question is, why doesn't Excel change the format of cells when
we change the format using 'Format cell' ? Odd, and frustrating!

Thanks Max.

mbabch...@gmail.com

unread,

Feb 7, 2014, 2:08:23 PM2/7/14

to

use the round formula to make it the same values. It is pretty stupid, but you can't see that it one of your values is off by just a slightest. no matter how many zero you place it out, it still wont show the rounding error.

jamie...@univ-tours.fr

unread,

Mar 9, 2017, 8:49:32 AM3/9/17

to

Hello - I realise this is an old thread but have a similar question and am totally lost - I have the following formula:

=VLOOKUP(B2,'Données'!$A$1:$E$20,3,FALSE)

Note that Données is a separate sheet within the same spreadsheet with data imported from another sheet.

No matter what I try (verifying number of characters, value of cell), I keep getting the error: "Did not find value 'AT203' in VLOOKUP evaluation." [AT203 being the value in cell B2].

Any help or advice?

Many thanks in advance.

GS

unread,

Mar 9, 2017, 11:19:34 AM3/9/17

to

How was the data imported? Does the lookup cell have extra spaces,
perhaps?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Dost Muhammad

unread,

Aug 5, 2021, 4:51:04 PM8/5/21

to

A few possible reasons your vlookup may be not finding the match are as follows:

1. Your lookup data must be sorted in alphabetical order in order for Excel to know which column to look up. 2. You need a comma in front of your cell range otherwise it is not within the table, and vlookup won't find the match either way, so why bother? 3. The number of columns you have for your matched data does not align with the number of columns you typed into the formula (e.g., if you have 5 matches but only typed 4 columns then one will not align)

Please see the following resource for a more detailed answer to this question.
https://www.exceltrick.com/formulas_macros/vlookup-in-excel/

gogoanime

unread,

Mar 5, 2023, 5:52:10 AMMar 5

to

Watch anime online in English., watch Gogoanime, anime online, free anime, english anime, sites to watch 9anime You watch free series and movies Chinese anime online and English subtitle gogoanime
<a href="https://ww2.gogoanime.com.co/">gogoanime</a>

gogoanime

unread,

Mar 5, 2023, 6:24:11 AMMar 5

to

On Sunday, 5 March 2023 at 15:52:10 UTC+5, gogoanime wrote:
> Watch anime online in English., watch Gogoanime, anime online, free anime, english anime, sites to watch 9anime You watch free series and movies Chinese anime online and English subtitle gogoanime

https://ww2.gogoanime.com.co

gogoanime

unread,

Mar 5, 2023, 6:24:30 AMMar 5

to

Khatron Ke Khiladi Colors Tv Hindi Show Full Episodes HD Quality, We Try Our Best To Make Available All Latest Episodes, Khatron Ke Khiladi Voot khatronkekhiladi

https://khatronkekhiladi.in/

gogoanime

unread,

Mar 5, 2023, 6:45:54 AMMar 5

to

gogoanime

unread,

Mar 5, 2023, 6:48:51 AMMar 5

to

Gogoanime Watch anime online in English. You can watch gogoanime free series and movies online in English subtitle.
"https://gogoanimes.li/

Kaleem Khan

unread,

Mar 5, 2023, 12:53:54 PMMar 5

to

Watch Pishachini Hindi Tv Serial Latest Today Episodes Online, Bollyfuntv, Desirulez, Desi serials, Desitvbox, Desitellybox, Yodesi, Apnetv, desitashion, desitvforum, play desi tv, By Colors Tv and Voot.
<a href="https://wwv.pishaachini.net/">Ishq Mein Ghayal</a>

Zeeshan Sajid

unread,

Mar 9, 2023, 8:02:05 AMMar 9

to

Watch korean drama movies and shows Online for free releases english sub in hd, watch asian drama, dramacool, drama cool, watch drama, english subtile, watch asian. https://dramacool9.com.co

itzy in

unread,

Mar 27, 2023, 5:19:01 AMMar 27

to

On Thursday, 9 March 2023 at 18:32:05 UTC+5:30,
If you need to grow your new website and rank in the search engine fast, you can check and enhance your website ranking with All SEO Tools
https://www.sarojmeher.com/
https://www.shridurgachalisa.com/
https://www.myfont.org/

Shahbaz Kaleem

unread,

Mar 28, 2023, 6:33:20 AMMar 28

to

Teri Meri Dooriyan Today Episode, Teri Meri Dooriyan <a href="https://terimeridoriyan.net/">Teri Meri Dooriyan </a> Watch Online, Teri Meri Dooriyan Latest Episode, Teri Meri Dooriyan Full Episode, Bollyfuntv, Apnetv, Desi Serials

Message has been deleted

Muhammad Atif

unread,

Mar 29, 2023, 10:31:30 AMMar 29

to

Muhammad Atif

unread,

Mar 29, 2023, 10:32:46 AMMar 29

to

Watch Online Teri Meri Dooriyan New Serial, Teri Meri Dooriyaan Today Episode, Teri Meri Doriyan Full Latest Episode Video by Hotstar

https://terimeridooriyan.net

Message has been deleted

Tamil Dhool

unread,

Apr 5, 2023, 6:04:59 AMApr 5

to

On Thursday, March 9, 2017 at 9:19:34 PM UTC+5, GS wrote:

Hi team, I watch the your website official EthirNeechal he is awesome and good information website.<a href="https://ethirneechal.net/">Ethir Neechal</a>

Message has been deleted

Tamil Dhool

unread,

Apr 5, 2023, 6:06:47 AMApr 5

to

I would like to say that Watch Sundari Full Episodes Watch Online in HD at Tamildhool in Tamil language because it is very nice site in tamil language. [url=https://tamildool.org/watch/category/sundari/]Sundari[/url]

Tamil Dhool

unread,

Apr 5, 2023, 6:08:04 AMApr 5

to

I would like to say that Tamildhool really convinced me to do it! that this very nice tamil show and programme site. Thanks, very good post. Watch Cool collection of Tamil Serial and TV shows at Tamildhool. You can watch, download and Share all Tamil Programmes for free on Tamil Dhool. https://tamildool.org/

Tamil Dhool

unread,

Apr 5, 2023, 6:08:23 AMApr 5

to

On Wednesday, April 5, 2023 at 3:06:47 PM UTC+5, Tamil Dhool wrote:

I would like to say that Watch Ethir Neechal Full Episodes Watch Online in HD at Tamildhool in Tamil language because it is very nice site in tamil language. https://tamildool.org/watch/category/ethir-neechal/

Tamil Dhool

unread,

Apr 5, 2023, 6:08:47 AMApr 5

to

I would like to say that Watch Priyamaana Thozhi Full Episodes Watch Online in HD at Tamildhool in Tamil language because it is very nice site in tamil language. https://tamildool.org/watch/category/priyamaana-thozhi/

Srif Tum

unread,

Apr 7, 2023, 1:15:35 AMApr 7

to

On Wednesday, April 5, 2023 at 3:08:47 PM UTC+5, Tamil Dhool wrote:
> On Wednesday, April 5, 2023 at 3:08:04 PM UTC+5, Tamil Dhool wrote:
> > On Wednesday, April 5, 2023 at 3:06:47 PM UTC+5, Tamil Dhool wrote:
> > > On Monday, March 27, 2023 at 2:19:01 PM UTC+5, itzy in wrote:
> > > > On Thursday, 9 March 2023 at 18:32:05 UTC+5:30,
> > > > If you need to grow your new website and rank in the search engine fast, you can check and enhance your website ranking with All SEO Tools

<a href="https://aashiqana3.net/" rel="noopener" target="_blank">Aashiqana 3 Watch Online</a> Full Episodes At Hotstar, Hindi Desi Serial <a href="https://aashiqana3.net/" rel="noopener" target="_blank">Aashiqana 3 Today Episode</a> Online Live At Voot, Watch Aashiqana 3 All Latest Episodes

> > > > https://www.sarojmeher.com/
> > > > https://www.shridurgachalisa.com/
> > > > https://www.myfont.org/
> > > I would like to say that Watch Sundari Full Episodes Watch Online in HD at Tamildhool in Tamil language because it is very nice site in tamil language. [url=https://tamildool.org/watch/category/sundari/]Sundari[/url]
> > I would like to say that Tamildhool really convinced me to do it! that this very nice tamil show and programme site. Thanks, very good post. Watch Cool collection of Tamil Serial and TV shows at Tamildhool. You can watch, download and Share all Tamil Programmes for free on Tamil Dhool. https://tamildool.org/

> I would like to say that Watch Priyamaana Thozhi Full Episodes Watch Online in HD at Tamildhool in Tamil language because it is very nice site in tamil language. https://tamildool.org/watch/category/priyamaana-thozhi/<a href="https://aashiqana3.net/" rel="noopener" target="_blank">Aashiqana 3 Watch Online</a> Full Episodes At Hotstar, Hindi Desi Serial <a href="https://aashiqana3.net/" rel="noopener" target="_blank">Aashiqana 3 Today Episode</a> Online Live At Voot, Watch Aashiqana 3 All Latest Episodes

Fgh Wer

unread,

Apr 9, 2023, 3:09:42 PMApr 9

to

Are You Finding a Best Drama Website for Stories & Entertainment On https://sapnokichhalaang.com.Here We are Available For collection of Indian TV shows, and web series. Now you Can Watch Papulor Dramas

>Sapno Ki Chhalaang
>Bade Achhe Lagte Hain
>Taarak Mehta Ka Ooltah Chashmah
>AliBaba Dastaan E Kabul
>Dhruv Tara
>Katha Ankahee

Full Episode HD 1080 4K Formate Pleas Visit https://sapnokichhalaang.com

ghlam farid

unread,

Apr 10, 2023, 12:43:49 PMApr 10

to

Bigg Boss 17 Watch Online Full latest episodes colors tv and voot serial. Hindi Drama desi Serial Bigg Boss Season 17 Show free download.

https://biggboss17show.com/

Fgh Wer

unread,

Apr 11, 2023, 4:09:22 AMApr 11

to