Lookup Value2

[Len]

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

 

[Macgru]

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

 

[Kassie]

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

 

[Max]

One way

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

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

Copy E1 down to E5

Adapt to suit ..

 

[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

 

[Len]

Hi Macgru,

It works perfectly well, thanks for your suggestion

Rdgs
Len

 

[Max]

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 <at>yahoo<dot>com, or
xdemechanik <at>yahoo<dot>com

 

[ltong]

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]

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.

 

[Max]

Hey, you're supposed to ask only Q per post ! <g>
Typo correction: The number "1" is missing in front of "Q"

 

[Len]

Typo correction: The number "1" is missing in front of "Q"

Hi Max

Thanks alot

Rdgs
Len

 

[Max]

You're welcome, Len !
Thanks for posting back
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Hi Max

Thanks alot

Rdgs
Len