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

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

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

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

What are the other columns of the two tables?

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

=dlookup(E2,$D$2:$D$54,$C$

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

$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
```

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

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... :)

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

where

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

í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...

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

Thanks

Manoj

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

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

Thanks,

tell me how to do

Thanks

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 ! :-)

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.

Saqib

Copy-of-bank-statement-1.xlsm

Copy-of-chequeissue-statement-a.xlsx