VLOOK UP is not working

[ADITYA]

Dear All,


Is there any formula alternate to Vlook up where I can serch amounts even though not in right side( vlook up works for figures where finding amount should right side only or else formula will not work)


Thanks for your help.

 

[bobhc]

Good day ADITYA


You could use choose


http://www.myonlinetraininghub.com/excel-vlookup-to-the-left-using-choose


or Index Match


http://www.myonlinetraininghub.com/excel-index-and-match-functions

 

[jeffreyweir]

Yes, you can use an INDEX/Match combo. There's a good guide at http://chandoo.org/wp/2012/03/30/comprehensive-guide-excel-vlookup/ which in turn has a link to http://chandoo.org/wp/2010/11/02/how-to-lookup-values-to-left/

 

[jeffreyweir]

bobhc: never seen that method of effectively reordering arrays with the CHOOSE function. Thanks for posting the link. Can't say I'd ever choose it (pun intended) over the INDEX/MATCH combo myself, but the trick is a good one to have up your sleeve in any case.

 

[bobhc]

Good day jeffreyweir


As the old saying goes "there is more than one way to skin a cat" and that is most definetly true with Excel.


Choose can make a left lookup a bit easyer.


You are making many contributions to the forum and seem to have a head for VBA which will be most usful, are we to refer to your good self as jeffreyweir or can we use a more friendly jeffrey?

 

[jeffreyweir]

Jeffrey or Jeff is fine, Bobhc. I just use JeffreyWeir over all the forums I post on so that people can see its the same Jeff.


Thanks for your kind words. Been really upskilling the VBA recently, and suddenly feel really fluent in it. One of the VBA projects I've enjoyed I recently made available via the Contextures blog at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/


Whoops...hijacking the thread. Better sign off now before someone throws a ninja star ;-)

 

[sgmpatnaik]

@Aditya


Hi Try the below UDF it will search both right side and left side

[pre]

Function vlukup(myvalue As Range, myrnge As Range, mycolumn As Variant) As String

Dim x As Range

For Each x In myrnge
If x.Value = myvalue.Value Then
vlukup = x.Offset(0, mycolumn).Value
Exit Function
End If
Next x
End Function

[/pre]

FORMULA


For Right Side:


=VLUKUP(A1,D2:D5,1) The result is coming of the Column E


For Left Side:


=VLUKUP(A1,D2:D5,-1) The Result is coming of the Column C


For Same range


=VLUKUP(A1,D2:D5,0) The Result is Coming from the Column D


Hope it will clear


Thanks


SP