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

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

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?

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

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

Select allOpen in new window

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

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

excellent dear

thanks very much

Manoj

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

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

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

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

Dear FernandoFernandes:

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

Thanks
Manoj

which Excel are you using ?

MS office 2007

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

Thanks a lot 2nd file work size 13kb

You save lot of time
Thanks again

Bye
Manoj

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,

how to do this i dont find update to this question to redistribute points

tell me how to do

Thanks
Manoj

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

Sorry Dear I will take care about this in future.

Sorry
Manoj

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.

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.

:)