Multiple criteria LOOKUP

[Leon]

Hello everyone,

I am trying to figure out how to execure a Lookup with multiple look up
criterias. This is my formula right now:
"=LOOKUP(A5,JOURNAL!$J:$J,JOURNAL!F:F)" I tried to use the and
function to say "=Lookup(and(A5,A10), ...)" But that errored out. My
goal right now is to have function look in a different sheet and if
there is a cell whose row meets both criteria it puts it on a different
page.
For example:

In Sheet 1 i have 50 rows of entries with the date, check number,
amount, account number, reference number, ... In Sheet 2 i have a
summary of Joe's account and i want to see if he has been paying me on
time. So i want to enter the reference number in cell A5 and in cell
B5 i have a Lookup function to lookup Sheet 1 and the criteria is if
the row has Joe's account number, and Reference number then i want it
to tell me the ammount. And in the next cell i would change the
formula to give me the date.

I hope that made sence. Email me if you have a question about what i'm
tryign to do.

Thank you all for the help.

Sincerely,
Leon

[Max]

One alternative to try ..

Assume the source table below is in Sheet1, cols A to E,
data from row2 down to say row100

Date Chq# Amt acct# Ref#
23-Dec-05 2222 198 1234 1111
24-Dec-05 3333 117 1235 1112
25-Dec-05 4444 196 1235 1113
26-Dec-05 5555 158 1234 1114
etc

In Sheet2,
we have the Ref# in A5: 1113
and the Acct# in A10: 1235

Then we could ..

Put in B5, and array-enter the formula
(i.e. press CTRL+SHIFT+ENTER):
=INDEX(Sheet1!$C$2:$C$100,MATCH(1,(Sheet1!$E$2:$E$100=A5)*(Sheet1!$D$2:$D$10
0=A10),0))

Put in C5 and array-enter the formula
(i.e. press CTRL+SHIFT+ENTER):
=INDEX(Sheet1!$A$2:$A$100,MATCH(1,(Sheet1!$E$2:$E$100=A5)*(Sheet1!$D$2:$D$10
0=A10),0))
Format C5 as date

For the sample source data,
B5 will return: 196 (the Amt)
C5 returns: 25-Dec-05 (the Date)

Adapt the ranges to suit the extent of the data in Sheet1
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Leon" <[email protected]> wrote in message
news:[email protected]...
> Hello everyone,
>
> I am trying to figure out how to execure a Lookup with multiple look up
> criterias. This is my formula right now:
> "=LOOKUP(A5,JOURNAL!$J:$J,JOURNAL!F:F)" I tried to use the and
> function to say "=Lookup(and(A5,A10), ...)" But that errored out. My
> goal right now is to have function look in a different sheet and if
> there is a cell whose row meets both criteria it puts it on a different
> page.
> For example:
>
> In Sheet 1 i have 50 rows of entries with the date, check number,
> amount, account number, reference number, ... In Sheet 2 i have a
> summary of Joe's account and i want to see if he has been paying me on
> time. So i want to enter the reference number in cell A5 and in cell
> B5 i have a Lookup function to lookup Sheet 1 and the criteria is if
> the row has Joe's account number, and Reference number then i want it
> to tell me the ammount. And in the next cell i would change the
> formula to give me the date.
>
> I hope that made sence. Email me if you have a question about what i'm
> tryign to do.
>
> Thank you all for the help.
>
> Sincerely,
> Leon
>

[CLR]

One way is to use a new helper column just to the left of your data table and
CONCATENATE the cells from the two columns of interest into the new
column......then look up the CONCATENATION of the two cells of interest in
this new column and step over to the values you seek. i would use VLOOKUP.

Vaya con Dios,
Chuck, CABGx3



"Leon" wrote:

> Hello everyone,
>
> I am trying to figure out how to execure a Lookup with multiple look up
> criterias. This is my formula right now:
> "=LOOKUP(A5,JOURNAL!$J:$J,JOURNAL!F:F)" I tried to use the and
> function to say "=Lookup(and(A5,A10), ...)" But that errored out. My
> goal right now is to have function look in a different sheet and if
> there is a cell whose row meets both criteria it puts it on a different
> page.
> For example:
>
> In Sheet 1 i have 50 rows of entries with the date, check number,
> amount, account number, reference number, ... In Sheet 2 i have a
> summary of Joe's account and i want to see if he has been paying me on
> time. So i want to enter the reference number in cell A5 and in cell
> B5 i have a Lookup function to lookup Sheet 1 and the criteria is if
> the row has Joe's account number, and Reference number then i want it
> to tell me the ammount. And in the next cell i would change the
> formula to give me the date.
>
> I hope that made sence. Email me if you have a question about what i'm
> tryign to do.
>
> Thank you all for the help.
>
> Sincerely,
> Leon
>
>