• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vlookup query [SOLVED]

R

rahulsharma012

New Member
i am sending the file as link.


my problem is that i am not getting the resultlt in G2 column when i am combining e2&f2 as lookup value.


http://www.fileconvoy.com/dfl.php?id=gdaa82ba510ec9933999301878322f2d5d45d5d9e5
 
Hi rahul,


Try this in G2, Vlookup will not work this way:


Code: 
=INDEX($C$2:$C$5,MATCH($A$2&$B$2,$A$2:$A$5&$B$2:$B$5,0),0)


Regards,
 
Hi Rahul,

[pre]
Code: 
First_Name	Last_Name	Grade
Jim		Brown		A
Billy		Jean		A
Sarah		Jones		B
Billy		Ray		C[/pre]

we we have a lot of option..  Simple one is..

[code]=INDEX($C$2:$C$5,MATCH($A2&$B2,$A$2:$A$5&$B$2:$B$5,0)) with Ctrl + Shift + Enter.


however.. if you want to stick with VLOOKUP.. try this..

{=VLOOKUP(E2&F2,CHOOSE({1,2},$A$2:$A$5&$B$2:$B$5,$C$2:$C$5),2,0)}


or


{=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$5&$B$2:$B$5,$C$2:$C$5),2,0)}[/code]


Regards,

Deb
 
Hi Faseeh..


he he.. after 40.. Sec.. Do you still believe.. VLOOKUP will not wotk this way.. :)


Regards,

Deb
 
Hi Deb,


Okkkk :) "Mr.Next_Excel_Ninja" Yup I knew you were working over it!! :p Sometimes i think i should restart learning process from zero when i see posts of Sajan, Shri & that of yours :)


Regards,
 
Hi Bhaijaan,


Thannks.. and By the way.. same here.. regarding Sajan & Shri.. :)


Regards,

Deb
 
by the way debraj in your above post i am not having any idea why you have use IF({1,0}.

what is this suggesting and why 1 and 0 why not any other number.

kindly clarify.


{=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$5&$B$2:$B$5,$C$2:$C$5),2,0)}


Regards,

Rahul
 
Hi Rahul..


good point.. lemme try to clear this.. :)


IF(check condition , True Part , False part)..


Now.. what if..

Check condition is both True & False.. I know.. in real life its not possible.. but in LOGICAL OPERATOR world.. its possible..

If a condition is both True & False also.. IF will gives you both..

In first Column : True Part($A$2:$A$5&$B$2:$B$5) &

In Second Column : False Part($C$2:$C$5)..


which will look like a table with two column..


& checking a Table return 2nd Column result is part of VlookUp..


Above all explanation are useless.. if I don't tell you.. that in BINARY WORLD..

1 Means TRUE & 0 means FALSE..


Hope its clear now.. :)


Regards,

Deb
 
NOW I AM USING THE BELOW MENTIONED FORMULA USING INDEX MATCH ITS SHOWING THE RESULT IN G2 BUT NOT IN G3 G4 AND G5.


=VLOOKUP(E2&F2,CHOOSE({1,2},$A$2:$A$5&$B$2:$B$5,$C$2:$C$5),2,0)
 
@Rahul


Hi


The formula is working in all given ranges like G3, G4 and more


if you are getting the Result as #N/A then you need to enter the G2 formula as Array (Ctrl + Shift + Enter) then copy down now you will get the result


Hope you understand


Thanks


SP
 
Hi Rahul..


One Useful tip..


Please Save Electricity..


Please turn off..


CAPS LOCK & NUM LOCK in case they are not in use..


on behalf of World Health Organization..


Regards,

Deb
 
Good day Debraj Roy


"on behalf of World Health Organization.." nice one, put a smile on my face
 
haahaha instead World Health Organization should take sincere steps to ban keyboard manufacturing companies from putting caps and num lock keys or everytime the user presses it they should charge caps and num lock keys pressed per stroke from manufacturing companies. hehehe


Regards,

Rahul
 
What about your problem

[pre]
Code: 
NOW I AM USING THE BELOW MENTIONED FORMULA USING INDEX MATCH ITS SHOWING THE RESULT IN G2 BUT NOT IN G3 G4 AND G5.

=VLOOKUP(E2&F2,CHOOSE({1,2},$A$2:$A$5&$B$2:$B$5,$C$2:$C$5),2,0)
[/pre]

is it worked or not


Thanks


SP
 
we told you to enter as a Array Formula


if still you are getting #N/A then please upload the file


we will rewrite in there


Thanks


SP
 
Hi Rahul..


above formula need to confirm be Ctrl + Shift + Enter not just Enter..


after confirm formula.. in Formula Bar.. you may found a curly Brackets {} is covering the whole Formula.


BTW, do you have any confusion on this formula..


Code: 
{=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$5&$B$2:$B$5,$C$2:$C$5),2,0)}


Regards,

Deb
 
You must log in or register to reply here.
Back
Top