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

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

tan_manojAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
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
 
Rob HensonFinance AnalystCommented:
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
 
FernandoFernandesCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
tan_manojAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
tan_manojAuthor Commented:
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
 
FernandoFernandesCommented:
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
 
tan_manojAuthor Commented:
excellent dear

thanks very much

Manoj
0
 
tan_manojAuthor Commented:
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
 
FernandoFernandesCommented:
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
 
tan_manojAuthor Commented:
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
 
FernandoFernandesCommented:
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
 
tan_manojAuthor Commented:
Dear FernandoFernandes:

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

Thanks
Manoj
0
 
FernandoFernandesCommented:
which Excel are you using ?
0
 
tan_manojAuthor Commented:
MS office 2007
0
 
FernandoFernandesCommented:
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
 
tan_manojAuthor Commented:
Thanks a lot 2nd file work size 13kb

You save lot of time
Thanks again

Bye
Manoj
0
 
FernandoFernandesCommented:
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
 
tan_manojAuthor Commented:
how to do this i dont find update to this question to redistribute points

tell me how to do

Thanks
Manoj
0
 
FernandoFernandesCommented:
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
 
tan_manojAuthor Commented:
Sorry Dear I will take care about this in future.

Sorry
Manoj
0
 
Saqib Husain, SyedEngineerCommented:
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
 
FernandoFernandesCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.