Hi,

How to solve a problem in vlookup formula if the lookup value is not an
unique number ?
e.g.
Sheet1
A B C D
1 01/03/2005 Miscellaneous Debit 100.00
2 01/03/2005 Miscellaneous Debit 9,006.30
3 15/03/2005 Cheques Debit 100.00
4 24/03/2005 Miscellaneous Debit 20.00
5 25/03/2005 Miscellaneous Debit 2,000.00

Sheet2
A B C D
1 9,006.30 100050 01/03/2005 GLP5-00696
2 100.00 100050 01/03/2005 GLP5-00697
3 20.00 600045 24/03/2005 GLP5-00699
4 2,000.00 155652 25/03/2005 GLP5-00652
5 3,350.00 155654 25/03/2005 GLP5-00654


If lookup value in Sheet1 is cell D1 = 100.00, the
vlookup(D1,Sheet2!$A$1:$C$5,1,FALSE) = 100.00
however, the vlookup formula will not give the correct result
especially if the lookup value in Sheet1 for cell D3 = 100.00, ie the
result still 100.00 as the value in A2 of Sheet2 is matched against the
value in D3 = 100.00 of Sheet1 where the
vlookup(D3,Sheet2!$A$1:$C$5,1,FALSE)= 100.00

Is there anyway to lookup 2 values at the same time, 1st value is the
value in D3 and the 2nd value is the date in A3, then apply the vlookup
formula ?
Lookup value Lookup Date Vlookup formula
a)D1 = 100.00 A1 = 01/03/2005 Result = 100.00 in Sheet2
in Sheet1 in Sheet1 (ie A2=100,C2=01/03/2005)

b)D2 = 9,006.30 A2 = 01/03/2005 Result = 9,006.30 in Sheet2
in Sheet1 in Sheet1 (ie A1=9,006.30,C1=01/03/2005)

c)D3 = 100.00 A3 = 15/03/2005 Result = Nil in Sheet2
in Sheet1 in Sheet1 (ie A1=100.00,C1=01/03/2005 not
matched)

kindly assist me to set a Excel formula or VBA code to find 2 lookup
values at the same time to solve the above problem

Thanks
Regards
Len

Uzytkownik "Len" napisal w wiadomosci
oups.com...
Hi,

How to solve a problem in vlookup formula if the lookup value is not an
unique number ?
e.g.
Sheet1
A B C D
1 01/03/2005 Miscellaneous Debit 100.00
2 01/03/2005 Miscellaneous Debit 9,006.30
3 15/03/2005 Cheques Debit 100.00
4 24/03/2005 Miscellaneous Debit 20.00
5 25/03/2005 Miscellaneous Debit 2,000.00

Sheet2
A B C D
1 9,006.30 100050 01/03/2005 GLP5-00696
2 100.00 100050 01/03/2005 GLP5-00697
3 20.00 600045 24/03/2005 GLP5-00699
4 2,000.00 155652 25/03/2005 GLP5-00652
5 3,350.00 155654 25/03/2005 GLP5-00654


If lookup value in Sheet1 is cell D1 = 100.00, the
vlookup(D1,Sheet2!$A$1:$C$5,1,FALSE) = 100.00
however, the vlookup formula will not give the correct result
especially if the lookup value in Sheet1 for cell D3 = 100.00, ie the
result still 100.00 as the value in A2 of Sheet2 is matched against the
value in D3 = 100.00 of Sheet1 where the
vlookup(D3,Sheet2!$A$1:$C$5,1,FALSE)= 100.00

Is there anyway to lookup 2 values at the same time, 1st value is the
value in D3 and the 2nd value is the date in A3, then apply the vlookup
formula ?
Lookup value Lookup Date Vlookup formula
a)D1 = 100.00 A1 = 01/03/2005 Result = 100.00 in Sheet2
in Sheet1 in Sheet1 (ie A2=100,C2=01/03/2005)

b)D2 = 9,006.30 A2 = 01/03/2005 Result = 9,006.30 in Sheet2
in Sheet1 in Sheet1 (ie A1=9,006.30,C1=01/03/2005)

c)D3 = 100.00 A3 = 15/03/2005 Result = Nil in Sheet2
in Sheet1 in Sheet1 (ie A1=100.00,C1=01/03/2005 not
matched)

kindly assist me to set a Excel formula or VBA code to find 2 lookup
values at the same time to solve the above problem

Thanks
Regards
Len


i would suggest to insert additional column with formula combining cells of
A & D columns
then use lookup looking for A&D value
mcg

In which cells do you want your lookup formulae? You are showing values in
all your cells, so it is rather difficult to figure out what it is you want
to do! And yes, you can look up based on 2 values. Only, you will be using
INDEX, iso LOOKUP

"Len" wrote:

Hi,

How to solve a problem in vlookup formula if the lookup value is not an
unique number ?
e.g.
Sheet1
A B C D
1 01/03/2005 Miscellaneous Debit 100.00
2 01/03/2005 Miscellaneous Debit 9,006.30
3 15/03/2005 Cheques Debit 100.00
4 24/03/2005 Miscellaneous Debit 20.00
5 25/03/2005 Miscellaneous Debit 2,000.00

Sheet2
A B C D
1 9,006.30 100050 01/03/2005 GLP5-00696
2 100.00 100050 01/03/2005 GLP5-00697
3 20.00 600045 24/03/2005 GLP5-00699
4 2,000.00 155652 25/03/2005 GLP5-00652
5 3,350.00 155654 25/03/2005 GLP5-00654


If lookup value in Sheet1 is cell D1 = 100.00, the
vlookup(D1,Sheet2!$A$1:$C$5,1,FALSE) = 100.00
however, the vlookup formula will not give the correct result
especially if the lookup value in Sheet1 for cell D3 = 100.00, ie the
result still 100.00 as the value in A2 of Sheet2 is matched against the
value in D3 = 100.00 of Sheet1 where the
vlookup(D3,Sheet2!$A$1:$C$5,1,FALSE)= 100.00

Is there anyway to lookup 2 values at the same time, 1st value is the
value in D3 and the 2nd value is the date in A3, then apply the vlookup
formula ?
Lookup value Lookup Date Vlookup formula
a)D1 = 100.00 A1 = 01/03/2005 Result = 100.00 in Sheet2
in Sheet1 in Sheet1 (ie A2=100,C2=01/03/2005)

b)D2 = 9,006.30 A2 = 01/03/2005 Result = 9,006.30 in Sheet2
in Sheet1 in Sheet1 (ie A1=9,006.30,C1=01/03/2005)

c)D3 = 100.00 A3 = 15/03/2005 Result = Nil in Sheet2
in Sheet1 in Sheet1 (ie A1=100.00,C1=01/03/2005 not
matched)

kindly assist me to set a Excel formula or VBA code to find 2 lookup
values at the same time to solve the above problem

Thanks
Regards
Len

One way

In Sheet1
---------
Try in E1, array-entered (Press CTRL+SHIFT+ENTER):

=IF(ISNA(MATCH(A1&"_"&D1,Sheet2!$C$1:$C$5&"_"&Shee t2!$A$1:$A$5,0)),"No
match",INDEX(Sheet2!$A$1:$A$5,MATCH(A1&"_"&D1,Shee t2!$C$1:$C$5&"_"&Sheet2!$A
$1:$A$5,0)))

Copy E1 down to E5

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Len" wrote in message
oups.com...
Hi,

How to solve a problem in vlookup formula if the lookup value is not an
unique number ?
e.g.
Sheet1
A B C D
1 01/03/2005 Miscellaneous Debit 100.00
2 01/03/2005 Miscellaneous Debit 9,006.30
3 15/03/2005 Cheques Debit 100.00
4 24/03/2005 Miscellaneous Debit 20.00
5 25/03/2005 Miscellaneous Debit 2,000.00

Sheet2
A B C D
1 9,006.30 100050 01/03/2005 GLP5-00696
2 100.00 100050 01/03/2005 GLP5-00697
3 20.00 600045 24/03/2005 GLP5-00699
4 2,000.00 155652 25/03/2005 GLP5-00652
5 3,350.00 155654 25/03/2005 GLP5-00654


If lookup value in Sheet1 is cell D1 = 100.00, the
vlookup(D1,Sheet2!$A$1:$C$5,1,FALSE) = 100.00
however, the vlookup formula will not give the correct result
especially if the lookup value in Sheet1 for cell D3 = 100.00, ie the
result still 100.00 as the value in A2 of Sheet2 is matched against the
value in D3 = 100.00 of Sheet1 where the
vlookup(D3,Sheet2!$A$1:$C$5,1,FALSE)= 100.00

Is there anyway to lookup 2 values at the same time, 1st value is the
value in D3 and the 2nd value is the date in A3, then apply the vlookup
formula ?
Lookup value Lookup Date Vlookup formula
a)D1 = 100.00 A1 = 01/03/2005 Result = 100.00 in Sheet2
in Sheet1 in Sheet1 (ie A2=100,C2=01/03/2005)

b)D2 = 9,006.30 A2 = 01/03/2005 Result = 9,006.30 in Sheet2
in Sheet1 in Sheet1 (ie A1=9,006.30,C1=01/03/2005)

c)D3 = 100.00 A3 = 15/03/2005 Result = Nil in Sheet2
in Sheet1 in Sheet1 (ie A1=100.00,C1=01/03/2005 not
matched)

kindly assist me to set a Excel formula or VBA code to find 2 lookup
values at the same time to solve the above problem

Thanks
Regards
Len

Hi Max,

First of all, thank you for your time to reply.
After several attempts to workaround to understand your formula that
placed in Sheet1 and it seems that the result for E1 to E5 still show
"No match".
Actually, I need the result to be in value that retrieve from column A
in Sheet2.

Rdgs
Len

Hi Macgru,

It works perfectly well, thanks for your suggestion

Rdgs
Len

The results in E1:E5 (Sheet1) based on the sample data
in the original post should be:

100
9006.3
No match
20
2000

Not sure what happened over there <g,
but if you want a working sample, just drop me a line at either:
demechanik <atyahoo<dotcom, or
xdemechanik <atyahoo<dotcom
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Len" wrote in message
ups.com...
Hi Max,

First of all, thank you for your time to reply.
After several attempts to workaround to understand your formula that
placed in Sheet1 and it seems that the result for E1 to E5 still show
"No match".
Actually, I need the result to be in value that retrieve from column A
in Sheet2.

Rdgs
Len

Hi Max,
Finally, I managed to get the result after reformating the date in
both sheets
Thanks anyway.

Appreciate if you could help me to clear my doubts : -
1) In your formula that contains "&_&" between the cells, eg
$A$2&_&$D$2, is it concatenated both cells. If so, how many cells can
be concatenated in that formula( ie the limitation)?
2)Can we use nested formula within vlookup or Index & Match formula ?
If so, what is the limitation ?
3) what is the difference between the excel formula, Vlookup and Index
& Match ?, In what situation Index & Match formula will apply and not
Vlookup, vice versa and when Index & Match formula will take
precendent over Vlookup ?

Thanks
Regards
Len







"Max" wrote in message ...
The results in E1:E5 (Sheet1) based on the sample data
in the original post should be:

100
9006.3
No match
20
2000

Not sure what happened over there <g,
but if you want a working sample, just drop me a line at either:
demechanik <atyahoo<dotcom, or
xdemechanik <atyahoo<dotcom
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Len" wrote in message
ups.com...
Hi Max,

First of all, thank you for your time to reply.
After several attempts to workaround to understand your formula that
placed in Sheet1 and it seems that the result for E1 to E5 still show
"No match".
Actually, I need the result to be in value that retrieve from column A
in Sheet2.

Rdgs
Len

"ltong" wrote

Hey, you're supposed to ask only Q per post ! <g

1) In your formula that contains "&_&" between the cells, eg
$A$2&_&$D$2, is it concatenated both cells.

Yes

If so, how many cells can
be concatenated in that formula( ie the limitation)?

I don't know, really. Think there's the nested IF limit and maybe a max
formula length limit (1024??) which might hit us pretty early on, though.
Perhaps more important is to know what the deuce is going on, i.e. the
primary purpose in concat is to establish a "unique-enough" string from
amongst the fields which can then be used for the matching. So, concat it
just enough.

2)Can we use nested formula within vlookup or Index & Match formula ?

Yes

If so, what is the limitation ?

As per the above thoughts

3) what is the difference between the excel formula, Vlookup and Index
& Match ?, In what situation Index & Match formula will apply and not
Vlookup, vice versa and when Index & Match formula will take
precendent over Vlookup ?

IMHO, think Vlookup is less versatile compared with Index & Match as it
requires the looked-up return cols to be to the right of the lookup col.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hey, you're supposed to ask only Q per post ! <g
Typo correction: The number "1" is missing in front of "Q"
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Max wrote:
Hey, you're supposed to ask only Q per post ! <g
Typo correction: The number "1" is missing in front of "Q"
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max

Thanks alot

Rdgs
Len

You're welcome, Len !
Thanks for posting back
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Len" wrote
Hi Max

Thanks alot

Rdgs
Len