Solved

How to use vlookup function in excel to fetch data from two column

Posted on 2011-03-23
23
320 Views
Last Modified: 2012-05-11
i want to use vlookup function in excel to fetch cheque no where amount match. i have two table

Table one (say table A)consist amonut only.  Table two (say table B) consist amount with cheque number.
 Now problem is that i use vlookup but there are 5 time 10000 amount in table A and 6 times 10000 amount in table B
 but when i fetch cheque no it give same cheque number against all the 5 times 10000 amount. please solove the prolem

0
Comment
Question by:tan_manoj
  • 10
  • 9
  • 3
  • +1
23 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 35198500
vlookup will only return the first match of the amount.

How would you manually determine which cheque number you require to be the result? Once we know some other criteria we may be able to help.

Cheers
Rob H
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35198748
Tan, you have to have a unique identifier between these two columns, and it seems like the value is not a good identifier, because it's not unique.

What are the other columns of the two tables?
0
 

Author Comment

by:tan_manoj
ID: 35198806
Dear FernandoFernandes:

Another column is Date in both table. One table cheque issue table date, cheque no, amount
second table have date and amount i have to pick cheque no from cheque issue table cheque issue table date is alwasy less then second table u can say first cheque then present in bank i have to prepare bank statment from cheque issue statement after matching amount only condition i can put date condition

thanks
Manoj
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35199211
You can use a UDF to do what you want as follows.

=dlookup(E2,$D$2:$D$54,$C$2:$C$54,$E$1:E1)

where e2 is cell having the amount to be looked up
D2:D54 is the range which contains the amounts
C2:C54 is the range which contains the check numbers corresponding to d2:d54
These arguments are similar to those in a vlookup except that it has been split into two. The ranges for amount and check numbers are specified separately as opposed to vlookup where they are given in one range.

$E$1:E1 is the main difference from vlookup. It refers to all values looked up prior to looking up the current amount. This is needed to know how many of these numbers have already been looked up previously. It is important to note that this range starts at one cell above all the values to be looked up and ends one cell above the current value to be looked up. So one of the E1's has the $ sign and the other has not. So when copied down this would change like this

$E$1:E1
$E$1:E2
$E$1:E3
$E$1:E4
.
.
.

Function dlookup(x, sr, tr, nr)
nm = WorksheetFunction.CountIf(nr, x)
If nm = 0 Then dlookup = tr.Cells(WorksheetFunction.Match(x, sr, 0), 1): Exit Function
nx = 0
For i = 1 To sr.Rows.Count
If sr.Cells(i, 1) = x Then nx = nx + 1
If nx = nm + 1 Then Exit For
Next i
If nx = nm + 1 Then dlookup = tr.Cells(i, 1): Exit Function
End Function

Open in new window

0
 

Author Comment

by:tan_manoj
ID: 35199582
Dear ssaqibh:

i attach two file one bank statement and 2nd one is check issue statement. please put this formula into bank statement.

Thanks
Manoj

bank-statement.xlsx
chequeissue-statement-a.xlsx
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35199693
Sorry Tan...
i guess I understand the issue.
But you have to understand that any solution provided, may work for most cases, but will never work for all cases, since you don't have an unique identifier between these two tables, right ?

I mean, the human factor is always going to be a need for any tech solution... so it will never be a fully automated process...

unless I am not understanding properly :(... in this case... I am anxious to see ssaqibh's replies... :)
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 35201550
The function I provided was not working across workbooks so I have now changed it and applied it to the given files

Saqib
Copy-of-bank-statement-1.xlsm
Copy-of-chequeissue-statement-a.xlsx
0
 

Author Comment

by:tan_manoj
ID: 35203882
excellent dear

thanks very much

Manoj
0
 

Author Comment

by:tan_manoj
ID: 35207615
Dear ssaqibh:

i want to know how to use dlookup. its use like vlookup just type =dlookup,d6,sheet2 etc or i have to writen through function you type above please tell me how to put these formula in excel

Thanks
 manoj
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35207687
any udf like the dlookup above, must be used form a cell like:

=dlookup(d6,range,range,range)

where range are cell ranges in your worksheet . ..
0
 

Author Comment

by:tan_manoj
ID: 35207924
Dear FernandoFernandes:

Can i copy this udf to any computer how to copy this udf and paste to any another computer to use any where.

Thanks
 Manoj

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35208065
ok, this UDF must be put in a module cod page...
ít will travel with the file you're using.

1) With Excel and your file open, press Alt+F11
2) Press Ctrl+R (to see the project explorer)
3) find your file in it, do a right click on it (anypart of it)
4) select Insert / module
5) paste ssaqibh's code in the page that will show up
6) Close this screen (VBE - Visual Basic Editor)

not this function will be available for this file only, everytime it is opened in any Excel...

If you want this function to be available in all computers regardless of the presence of your file, or if it is or is not opened, then you'll have to put it in an addin file and distribute to all computers... let me know if you need help if this is the case...
0
 

Author Comment

by:tan_manoj
ID: 35208118
Dear FernandoFernandes:

I want to put it in all computers how to put it in addin file or any other solution.

Thanks
Manoj
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35208169
which Excel are you using ?
0
 

Author Comment

by:tan_manoj
ID: 35208175
MS office 2007
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35208228
you have to from within each computer, install the XLAM file attached to this post.
to install:
1) Open Excel
2) office button
3) Add-ins
4) Excel Add-ins / Go
5) Browse
6) Fine the file which you saved somewhere
7) done !
for any file that you open (including the brand new book1 from when you open Excel), try to use in a cell:
=dlookup( all the parameters as stated before )

hopefully it will work...
i never tested the function, i hope it does exactly what you want...
DLOOKUP-ADDIN.xla
DLOOKUP-ADDIN.xlam
0
 

Author Comment

by:tan_manoj
ID: 35208299
Thanks a lot 2nd file work size 13kb

You save lot of time
Thanks again

Bye
Manoj
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35208324
Just one more thing, please update the question to redistribute points... it was a shared solution !

ssaqibh deserves the accepted solution indeed, I deserve assisted solution :)

Thanks,
0
 

Author Comment

by:tan_manoj
ID: 35208376
how to do this i dont find update to this question to redistribute points

tell me how to do

Thanks
Manoj
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35208409
it's ok Manoj...
no need to ... I'm glad I helped :-)

I'll get points in many other questions anyway :-)

just one thing, if you have more questions about this problem you were having (or any other), please create a new question, and put the link to this one.... this will make it more fair for the other experts who will answer ....

no need to worry about this one ! take care ! :-)
0
 

Author Comment

by:tan_manoj
ID: 35208431
Sorry Dear I will take care about this in future.

Sorry
Manoj
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35209500
The solution provided by me was complete as far as the question is concerned. The ability to distribute this among other computers is an altogether a different question. And the solution provided by FernandoFernandes is complete in its own domain. In all fairness, Manoj, you should open a new question and the title should include something like "Points for FernandoFernandes" in addition to the real title so that someoneelse does not attempt it. This way FF gets his well-deserved points.
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35209702
ssaqibh, this would be much appteciated, but the rules of EE do not allow questions for points transfering like that. So we shkuld leave it as is.

:)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now