Home

VLOOKUP returning the same value which is "being looked for".

%3CLINGO-SUB%20id%3D%22lingo-sub-691898%22%20slang%3D%22en-US%22%3EVLOOKUP%20returning%20the%20same%20value%20which%20is%20%26amp%3Bquot%3Bbeing%20looked%20for%26amp%3Bquot%3B.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691898%22%20slang%3D%22en-US%22%3E%3CP%3EStarted%20using%20VLOOKUP%20yesterday.%20Used%20it%20for%20the%20first%20time.%20Now%20when%20applying%20it%20to%20the%20following%20scenario%2C%20%3CSTRONG%3Eit's%20returning%20the%20same%20value%20as%20the%20result%2C%20which%20I%20am%20%22looking%20for%22%20in%20the%20other%20column.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CH2%20id%3D%22toc-hId-1767447209%22%20id%3D%22toc-hId-1767447891%22%20id%3D%22toc-hId-1767447891%22%20id%3D%22toc-hId-1767447891%22%20id%3D%22toc-hId-1767447891%22%20id%3D%22toc-hId-1767447891%22%3E%3CSTRONG%3EThat%20is%2C%20I%20am%20trying%20to%20find%20the%20names%20(as%20listed%20in%20the%20second%20column)%2C%20corresponding%20to%20the%20numbers%20in%20the%20last%20column.%20(first%20two%20column%20is%20the%20data%20which%20I%20already%20have%20at%20my%20disposal)%20and%20it%20is%20the%20last%20column%2C%20corresponding%20to%20which%2C%20I%20have%20to%20find%20the%20names%2C%20as%20listed%20in%20the%20second%20column.%3C%2FSTRONG%3E%3C%2FH2%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CH1%20id%3D%22toc-hId--588196247%22%20id%3D%22toc-hId--588195565%22%20id%3D%22toc-hId--588195565%22%20id%3D%22toc-hId--588195565%22%20id%3D%22toc-hId--588195565%22%20id%3D%22toc-hId--588195565%22%3E%3CSTRONG%3ENote%3A%20%3CU%3ETaken%20only%203%20rows%20of%20data%2C%20however%2C%20the%20data%20consists%20of%2050%20entries%20in%20the%20first%202%20columns.%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FH1%3E%3CH1%20id%3D%22toc-hId-1154614088%22%20id%3D%22toc-hId-1154614770%22%20id%3D%22toc-hId-1154614770%22%20id%3D%22toc-hId-1154614770%22%20id%3D%22toc-hId-1154614770%22%20id%3D%22toc-hId-1154614770%22%3E%26nbsp%3B%3C%2FH1%3E%3CH1%20id%3D%22toc-hId--1397542873%22%20id%3D%22toc-hId--1397542191%22%20id%3D%22toc-hId--1397542191%22%20id%3D%22toc-hId--1397542191%22%20id%3D%22toc-hId--1397542191%22%20id%3D%22toc-hId--1397542191%22%3E%3CSTRONG%3E%3CU%3EIn%20the%20last%20column%2C%20I%20have%2049%20entries%20in%20all%20and%20I%20am%20supposed%20to%20look%20up%20the%20values%20of%20the%20last%20column%2C%20within%20the%20first%20column%2C%20and%20get%20the%20corresponding%20names%20from%20the%20second%20column.%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FH1%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3ELet's%20name%20the%20existing%20columns%20as%20A%2C%20B%2C%20C%2C%20D%2C%20E%2C%20F%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3EI%20started%20appluing%20the%20Vlookup%20in%20C1%2C%20by%20placing%20the%20cursor%20in%20it%2C%20as%20follows%3A%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(F1%2C%20A1%3AB50%2C1%2C0)%20(as%20it%20is%20already%20mentioned%20that%20A%20and%20B%20columns%20have%2050%20data%20points.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EResult%20obtained%20in%20column%20C%3A%20I%20am%20getting%20the%26nbsp%3B%20corresponding%20values%20of%20column%20F%20itself%2C%26nbsp%3B%20in%20majority%20(matching%20with%20each%20other%2C%20wherever%20obtained)%2C%20else%20NA.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20tried%20using%20without%20the%20excat%20match%2C%20that%20is%20without%20zero%2C%20it%20again%20produces%20the%20numbers%20in%20column%20c%2C%20as%20listed%20in%20column%20f%2C%20%3CSTRONG%3Ebut%20this%20time%20producing%20even%20more%20absurd%20results%2C%20as%20there%20are%20no%20matches%20or%20corresponding%20values%20produced%20(as%20in%20the%20above%20case)%3C%2FSTRONG%3E%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E2658820821%3C%2FTD%3E%3CTD%3EMBAW%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1540217574%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1540217574%3C%2FTD%3E%3CTD%3EMBMS%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2658820821%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3514190626%3C%2FTD%3E%3CTD%3EMBGAM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E3514190626%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-691898%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-691954%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20returning%20the%20same%20value%20which%20is%20%26amp%3Bquot%3Bbeing%20looked%20for%26amp%3Bquot%3B.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691954%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20you%20have%20A1%20to%20B50%20and%20they%20are%20the%20codes%20and%20names%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20you%20have%20a%20list%20in%20column%20F%20that%20you%20need%20to%20lookup%20the%20names%20from%20that%20A1%20%3A%20B50%20List%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20your%20lookup%20in%20C1%20would%20be%3CBR%20%2F%3E%3CBR%20%2F%3E%3DVLOOKUP(%20F1%2C%20%24A%241%3A%24B%2450%2C%202%2C%20FALSE)%3CBR%20%2F%3E%3CBR%20%2F%3EDoes%20that%20give%20you%20what%20you%20need%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-691985%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20returning%20the%20same%20value%20which%20is%20%26amp%3Bquot%3Bbeing%20looked%20for%26amp%3Bquot%3B.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691985%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%2C%20Thanks%20for%20replying%2C%20yes%2C%20it%20is%20giving%20the%20values%2C%20when%20I%20had%20put%20B50%2C2%2C0.................%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20initially%2C%20when%20I%20was%20using%20B50%2C1%2C0....it%20was%20just%20reproducing%20the%20values%20in%20column%20F.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20please%20help%20me%20with%20the%20logic%20behind%20putting%20B50%2C2%2C0%20and%20not%20B50%2C%201%2C0.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20please%20note%20that%20I%20do%20not%20have%20any%20headers%20in%20the%20respective%20columns%2C%20hence%2C%20each%20of%20the%20columns%20start%20from%20first%20row%20itself%2C%20ie.%20A1%2C%20B1%2C%20F1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERemark%3A%20To%20some%20extent%20I%20had%20just%20mugged%20up%20one%20way%20of%20applying%20Vlookup%20after%20seeing%20an%20example%20through%20Google%2C%20hence%2C%20not%20very%20sure%20about%20the%20logic%20behind%20B50%2C2%2C0%20and%20not%20B50%2C1%2C0.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3Eexceluser%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-691994%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20returning%20the%20same%20value%20which%20is%20%26amp%3Bquot%3Bbeing%20looked%20for%26amp%3Bquot%3B.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691994%22%20slang%3D%22en-US%22%3ESo%20VLOOKUP%20works%20by%20%3CBR%20%2F%3Ea)%20referring%20to%20a%20value%20you%20want%20to%20lookup%20e.g.%20F1%3CBR%20%2F%3E%3CBR%20%2F%3Eb)%20and%20then%20referencing%20a%20range%20of%20data%20e.g.%20A1%20to%20B50%20where%20the%20frst%20column%20contains%20the%20values%20you%20are%20trying%20to%20match%20against%20with%20F1%3CBR%20%2F%3E%3CBR%20%2F%3Ec)%20then%20give%20it%20a%20column%20number%20that%20you%20want%20to%20return%20from%20the%20range%20of%20data.%20e.g.%20the%20name%20is%20in%20the%202nd%20column%20of%20the%20range%20A1%3AB50%20so%20you%20type%202%3CBR%20%2F%3E%3CBR%20%2F%3Ed)%20type%20FALSE%20for%20an%20exact%20match%2C%20never%20use%201%20or%20TRUE%20unless%20you%20know%20exactly%20why%20you%20are%20using%20them%3CBR%20%2F%3E%3CBR%20%2F%3EI'd%20also%20suggest%20you%20look%20at%20learning%20INDEX%20MATCH%20which%20is%20more%20flexible%20and%20lacks%20some%20of%20the%20risks%20of%20using%20VLOOKUP%3CBR%20%2F%3E%3CBR%20%2F%3EHere's%20a%20quick%20vidoe%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Faccessanalytic.com.au%2Findex-match%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Faccessanalytic.com.au%2Findex-match%2F%3C%2FA%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
learningexcel
Occasional Contributor

Started using VLOOKUP yesterday. Used it for the first time. Now when applying it to the following scenario, it's returning the same value as the result, which I am "looking for" in the other column. 

 

That is, I am trying to find the names (as listed in the second column), corresponding to the numbers in the last column. (first two column is the data which I already have at my disposal) and it is the last column, corresponding to which, I have to find the names, as listed in the second column.

 

Note: Taken only 3 rows of data, however, the data consists of 50 entries in the first 2 columns.

 

In the last column, I have 49 entries in all and I am supposed to look up the values of the last column, within the first column, and get the corresponding names from the second column.

 

Let's name the existing columns as A, B, C, D, E, F

 

I started appluing the Vlookup in C1, by placing the cursor in it, as follows:

 

=VLOOKUP(F1, A1:B50,1,0) (as it is already mentioned that A and B columns have 50 data points.

 

Result obtained in column C: I am getting the  corresponding values of column F itself,  in majority (matching with each other, wherever obtained), else NA.

 

when tried using without the excat match, that is without zero, it again produces the numbers in column c, as listed in column f, but this time producing even more absurd results, as there are no matches or corresponding values produced (as in the above case)

2658820821MBAW   1540217574
1540217574MBMS   2658820821
3514190626MBGAM   3514190626

 

3 Replies
Hi

So you have A1 to B50 and they are the codes and names

Then you have a list in column F that you need to lookup the names from that A1 : B50 List

So your lookup in C1 would be

=VLOOKUP( F1, $A$1:$B$50, 2, FALSE)

Does that give you what you need?


Hey @Wyn Hopkins , Thanks for replying, yes, it is giving the values, when I had put B50,2,0.................

 

However, initially, when I was using B50,1,0....it was just reproducing the values in column F.

 

Now, please help me with the logic behind putting B50,2,0 and not B50, 1,0.

 

 

Also, please note that I do not have any headers in the respective columns, hence, each of the columns start from first row itself, ie. A1, B1, F1.

 

 

Remark: To some extent I had just mugged up one way of applying Vlookup after seeing an example through Google, hence, not very sure about the logic behind B50,2,0 and not B50,1,0.

 

 

Regards,

exceluser

So VLOOKUP works by
a) referring to a value you want to lookup e.g. F1

b) and then referencing a range of data e.g. A1 to B50 where the frst column contains the values you are trying to match against with F1

c) then give it a column number that you want to return from the range of data. e.g. the name is in the 2nd column of the range A1:B50 so you type 2

d) type FALSE for an exact match, never use 1 or TRUE unless you know exactly why you are using them

I'd also suggest you look at learning INDEX MATCH which is more flexible and lacks some of the risks of using VLOOKUP

Here's a quick vidoe
https://accessanalytic.com.au/index-match/
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies