Home

Vlookup Problem

%3CLINGO-SUB%20id%3D%22lingo-sub-481179%22%20slang%3D%22en-US%22%3EVlookup%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481179%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20use%20%3Dvlookup%20to%20show%20phone%20numbers%20in%20an%20excel%20spread%20sheet%20that%20has%20the%20names%20in%20column%20a%20and%20the%20numbers%20in%20column%20b.%20It%20couldn't%20be%20more%20simple.%3C%2FP%3E%3CP%3EIf%20I%20type%20the%20name%20in%20cell%20a5%2C%20it%20will%20show%20the%20number%20in%20a4.%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20argument%20is%2C%20%3Dvlookup(d2%2Ca2%3Ab170%2C2)%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20doesn't%20get%20more%20basic%20than%20that%20but%20it%20is%20not%20working%20correctly.%20I'm%20using%20Office%20365.%3C%2FP%3E%3CP%3EWhat%20could%20possible%20be%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-481179%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481582%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481582%22%20slang%3D%22en-US%22%3EYour%20right.%20I%20didn't%20put%20False%20at%20the%20end%20because%20I%20was%20hoping%20that%20just%20putting%20a%20last%20name%20would%20be%20enough%20for%20it%20to%20approximate%20the%20corresponding%20number%20in%20the%20cell%20in%20the%20b%20column%20next%20to%20it.%20It%20works%20sometimes.%20If%20I%20put%20false%20at%20the%20end%20of%20the%20argument%2C%20I%20have%20to%20write%20exactly%20what's%20in%20the%20a%20column%20cell%20but%20it%20works%20every%20time.%20It's%20just%20not%20forgiving%20enough%20to%20be%20user%20friendly.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481444%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325168%22%20target%3D%22_blank%22%3E%40williep%3C%2FA%3E%26nbsp%3B%2C%20since%20you%20skip%20last%20parameter%2C%20VLOOKUP%20uses%20approximate%20search%20by%20default.%20And%20approximate%20search%20requires%20what%20your%20first%20column%20is%20to%20be%20sorted%20A%20to%20Z.%20Perhaps%20that's%20the%20reason.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481427%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481427%22%20slang%3D%22en-US%22%3EThat's%20correct.%20I%20wrote%20a4%20by%20mistake.%20It%20should%20be%20b4%20.%20So%20when%20I%20type%20in%20a%20name%20from%20a5%20it%20is%20returning%20a%20number%20from%20b4.%20Something%20is%20not%20working%20correctly.%20Also%2C%20some%20cells%20work%20ok.%20but%20the%20ones%20that%20don't%20will%20always%20return%20a%20number%20from%20the%20cell%20right%20above%20the%20correct%20cell.%3CBR%20%2F%3EI%20can't%20believe%20that%20I%20am%20the%20only%20one%20who%20has%20had%20this%20problem.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481194%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325168%22%20target%3D%22_blank%22%3E%40williep%3C%2FA%3E%26nbsp%3B%2C%20if%20in%20D2%20the%20name%20which%20is%20in%20A5%2C%20the%20formula%20shall%20return%20you%20the%20value%20from%20B5%2C%20isn't%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
williep
New Contributor

I am trying to use =vlookup to show phone numbers in an excel spread sheet that has the names in column a and the numbers in column b. It couldn't be more simple.

If I type the name in cell a5, it will show the number in a4.   

The argument is, =vlookup(d2,a2:b170,2)   

It doesn't get more basic than that but it is not working correctly. I'm using Office 365.

What could possible be wrong?

4 Replies

@williep , if in D2 the name which is in A5, the formula shall return you the value from B5, isn't it?

That's correct. I wrote a4 by mistake. It should be b4 . So when I type in a name from a5 it is returning a number from b4. Something is not working correctly. Also, some cells work ok. but the ones that don't will always return a number from the cell right above the correct cell.
I can't believe that I am the only one who has had this problem.

@williep , since you skip last parameter, VLOOKUP uses approximate search by default. And approximate search requires what your first column is to be sorted A to Z. Perhaps that's the reason.

Your right. I didn't put False at the end because I was hoping that just putting a last name would be enough for it to approximate the corresponding number in the cell in the b column next to it. It works sometimes. If I put false at the end of the argument, I have to write exactly what's in the a column cell but it works every time. It's just not forgiving enough to be user friendly.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies