Hi all,

I hope someone can help me with this. I want to return a value from a table
which is based upon two criteria. I have a table like this:

A B C
Account No. Date Balance
12345 23-4-09 100
56847 18-5-09 50
12345 18-5-09 75
32654 30-6-09 125

I want to do something like =VLOOKUP((A2 and B2:B5=18-5-09),A2:C5,3,FALSE)
to return 75.

Thanks in advance and regards,

Andrew

Hello Andrew,

If E1 contains your Account no search value and D1 your Date search
value:
=INDEX(C2:C5,MATCH(D1&"|"&E1,A2:A5&"|"&B2:B5,0))

The "|" construct protects you against lookup values like
54321 and 2-4-09
vs.
5432 and 12-4-09

Regards,
Bernd

Hi,

=INDEX(C1:C5,MATCH(1,(A1:A5=12345)*(B1:B5=DATE(200 9,5,18)),0))

In practice I'd use cell references for the lookup values

Mike

"Andrew Mackenzie" wrote:

Hi all,

I hope someone can help me with this. I want to return a value from a table
which is based upon two criteria. I have a table like this:

A B C
Account No. Date Balance
12345 23-4-09 100
56847 18-5-09 50
12345 18-5-09 75
32654 30-6-09 125

I want to do something like =VLOOKUP((A2 and B2:B5=18-5-09),A2:C5,3,FALSE)
to return 75.

Thanks in advance and regards,

Andrew




.

Forgot to mention:-

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Mike H" wrote:

Hi,

=INDEX(C1:C5,MATCH(1,(A1:A5=12345)*(B1:B5=DATE(200 9,5,18)),0))

In practice I'd use cell references for the lookup values

Mike

"Andrew Mackenzie" wrote:

Hi all,

I hope someone can help me with this. I want to return a value from a table
which is based upon two criteria. I have a table like this:

A B C
Account No. Date Balance
12345 23-4-09 100
56847 18-5-09 50
12345 18-5-09 75
32654 30-6-09 125

I want to do something like =VLOOKUP((A2 and B2:B5=18-5-09),A2:C5,3,FALSE)
to return 75.

Thanks in advance and regards,

Andrew




.

Try this

=INDEX(C2:C5,MATCH(D1&N(E1),A2:A5&B2:B5,0),0)

where D1 = 12345, E1 = 18/5/09

use Ctrl + shift + enter


On Oct 23, 2:59*pm, "Andrew Mackenzie"
wrote:
Hi all,

I hope someone can help me with this. *I want to return a value from a table
which is based upon two criteria. *I have a table like this:

* * * A * * * * * * * *B * * * * * * * *C
Account No. * *Date * * * * * *Balance
12345 * * * * * * *23-4-09 * * *100
56847 * * * * * * *18-5-09 * * * *50
12345 * * * * * * *18-5-09 * * * *75
32654 * * * * * * *30-6-09 * * *125

I want to do something like =VLOOKUP((A2 and B2:B5=18-5-09),A2:C5,3,FALSE)
to return 75.

Thanks in advance and regards,

Andrew

Thaaks for your help guys but all three solutions reurn #N/A. Definitely
entered as an array.

Any other ideas, the life is slowly being sapped out of me at the moment!

Cheers,
Andrew




"Andrew Mackenzie" wrote in message
...
Hi all,

I hope someone can help me with this. I want to return a value from a
table
which is based upon two criteria. I have a table like this:

A B C
Account No. Date Balance
12345 23-4-09 100
56847 18-5-09 50
12345 18-5-09 75
32654 30-6-09 125

I want to do something like =VLOOKUP((A2 and B2:B5=18-5-09),A2:C5,3,FALSE)
to return 75.

Thanks in advance and regards,

Andrew

Is your dates in col B are in Date format ?


On Oct 23, 4:33*pm, "Andrew Mackenzie"
wrote:
Thaaks for your help guys but all three solutions reurn #N/A. *Definitely
entered as an array.

Any other ideas, the life is slowly being sapped out of me at the moment!

Cheers,
Andrew

"Andrew Mackenzie" wrote in message

...

Hi all,

I hope someone can help me with this. *I want to return a value from a
table
which is based upon two criteria. *I have a table like this:

* * * A * * * * * * * *B * * * * * * * *C
Account No. * *Date * * * * * *Balance
12345 * * * * * * *23-4-09 * * *100
56847 * * * * * * *18-5-09 * * * *50
12345 * * * * * * *18-5-09 * * * *75
32654 * * * * * * *30-6-09 * * *125

I want to do something like =VLOOKUP((A2 and B2:B5=18-5-09),A2:C5,3,FALSE)
to return 75.

Thanks in advance and regards,

Andrew

Yes they are in date format.
"muddan madhu" wrote in message
...
Is your dates in col B are in Date format ?


On Oct 23, 4:33 pm, "Andrew Mackenzie"
wrote:
Thaaks for your help guys but all three solutions reurn #N/A. Definitely
entered as an array.

Any other ideas, the life is slowly being sapped out of me at the moment!

Cheers,
Andrew

"Andrew Mackenzie" wrote in message

...

Hi all,

I hope someone can help me with this. I want to return a value from a
table
which is based upon two criteria. I have a table like this:

A B C
Account No. Date Balance
12345 23-4-09 100
56847 18-5-09 50
12345 18-5-09 75
32654 30-6-09 125

I want to do something like =VLOOKUP((A2 and
B2:B5=18-5-09),A2:C5,3,FALSE)
to return 75.

Thanks in advance and regards,

Andrew

try this

=INDEX(C2:C5,MATCH(D1&DATE(2009,5,18),A2:A5&B2:B5, 0),0)




On Oct 23, 6:23*pm, "Andrew Mackenzie"
wrote:
Yes they are in date format."muddan madhu" wrote in message

...
Is your dates in col B are in Date format ?

On Oct 23, 4:33 pm, "Andrew Mackenzie"
wrote:

Thaaks for your help guys but all three solutions reurn #N/A. Definitely
entered as an array.

Any other ideas, the life is slowly being sapped out of me at the moment!

Cheers,
Andrew

"Andrew Mackenzie" wrote in message

...

Hi all,

I hope someone can help me with this. I want to return a value from a
table
which is based upon two criteria. I have a table like this:

A B C
Account No. Date Balance
12345 23-4-09 100
56847 18-5-09 50
12345 18-5-09 75
32654 30-6-09 125

I want to do something like =VLOOKUP((A2 and

B2:B5=18-5-09),A2:C5,3,FALSE)

to return 75.

Thanks in advance and regards,

Andrew

Thanks very much for your help. Unfortunately I could not get any of your
solutions to work for. In the end I created a helper column which basically
took the square root of the account number and added the date to get a
unique identifier and then used this in a straightforward index/match
function.

Cheers anyway,

Andrew
"Andrew Mackenzie" wrote in message
...
Hi all,

I hope someone can help me with this. I want to return a value from a
table
which is based upon two criteria. I have a table like this:

A B C
Account No. Date Balance
12345 23-4-09 100
56847 18-5-09 50
12345 18-5-09 75
32654 30-6-09 125

I want to do something like =VLOOKUP((A2 and B2:B5=18-5-09),A2:C5,3,FALSE)
to return 75.

Thanks in advance and regards,

Andrew

Hello again,

Switch D1 and E1 and array-enter the formula. Works for me...

Regards,
Bernd