lookup with 2 criteria
 

I'm working on utility data for a chain of stores. I need to lookup an
account number based on both facility # and meter # b/c some facilities have
multiple meters.

This formula only matches the account # and does not cross reference meter #.
=VLOOKUP(A96,Gaps!$A$8:$D$2102,3)

A96 = fac # on sheet 2
Gaps!A8:D2102 = where data is kept
On gaps page, fac # is column A, account # is column C, meter # is column D

Thanks in advance for any words of wisdom!

Hi
try the following array formula entered with CTRL+SHIFT+ENTER):
=INDEX(Gaps!$C$8:$C$2102,MATCH(1,(Gaps!$A$8:$A$210 2=A96)*(Gaps!$D$8:$D$2102=B96),0))

where B96 stores the searched meter#

--
Regards
Frank Kabel
Frankfurt, Germany
"LoriM" schrieb im Newsbeitrag
...
I'm working on utility data for a chain of stores. I need to lookup an
account number based on both facility # and meter # b/c some facilities
have
multiple meters.

This formula only matches the account # and does not cross reference meter
#.
=VLOOKUP(A96,Gaps!$A$8:$D$2102,3)

A96 = fac # on sheet 2
Gaps!A8:D2102 = where data is kept
On gaps page, fac # is column A, account # is column C, meter # is column
D

Thanks in advance for any words of wisdom!

Thank you so much Frank for your response. This may seem elementary to you
but to me...you're a genius! I will research Index & Match to learn them more
and hopefully be able to implement them in the future. Thanks again!

Lori

"Frank Kabel" wrote:

Hi
try the following array formula entered with CTRL+SHIFT+ENTER):
=INDEX(Gaps!$C$8:$C$2102,MATCH(1,(Gaps!$A$8:$A$210 2=A96)*(Gaps!$D$8:$D$2102=B96),0))

where B96 stores the searched meter#

--
Regards
Frank Kabel
Frankfurt, Germany
"LoriM" schrieb im Newsbeitrag
...
I'm working on utility data for a chain of stores. I need to lookup an
account number based on both facility # and meter # b/c some facilities
have
multiple meters.

This formula only matches the account # and does not cross reference meter
#.
=VLOOKUP(A96,Gaps!$A$8:$D$2102,3)

A96 = fac # on sheet 2
Gaps!A8:D2102 = where data is kept
On gaps page, fac # is column A, account # is column C, meter # is column
D

Thanks in advance for any words of wisdom!

=INDEX(Gaps!$C$9:$C$2103,MATCH(1,(Gaps!$A$9:$A$210 3=A2)*(Gaps!$D$9:$D$2103=F2),0))
works great for getting me the correct acct #. Thank you again Frank!

But now I need to take it a step further... The data that I'm working with
will eventually be imported into another database program where we already
have some of this data. We do not want duplicate data so I need to delete out
what has already been input to the database.

On my main sheet, cell P2 contains the month # for that invoice.
On Gaps page my columns look like this:

A B C D E F G H I
Site Fuel Acct Meter Feb'04 Mar Apr May June
0001 Electricity 10241 E0001-1 4 5

If the data has already been entered into the database, I have manually
placed the month number in the column. Now the tricky part.

On my main sheet, I need to have Period (cell P2) cross-referenced with that
particular account/meter on Gaps page and tell me if we already have the
data. My goal is to create a column at end (column R
) marking the rows as delete if I already have the information.

I have searched & searched help, communities, etc. Perhaps I am not
searching for the correct text.
Thanks so much for any response -- very, very appreciated!

"Frank Kabel" wrote:

Hi
try the following array formula entered with CTRL+SHIFT+ENTER):
=INDEX(Gaps!$C$8:$C$2102,MATCH(1,(Gaps!$A$8:$A$210 2=A96)*(Gaps!$D$8:$D$2102=B96),0))

where B96 stores the searched meter#

--
Regards
Frank Kabel
Frankfurt, Germany
"LoriM" schrieb im Newsbeitrag
...
I'm working on utility data for a chain of stores. I need to lookup an
account number based on both facility # and meter # b/c some facilities
have
multiple meters.

This formula only matches the account # and does not cross reference meter
#.
=VLOOKUP(A96,Gaps!$A$8:$D$2102,3)

A96 = fac # on sheet 2
Gaps!A8:D2102 = where data is kept
On gaps page, fac # is column A, account # is column C, meter # is column
D

Thanks in advance for any words of wisdom!

The array formula was extremely useful and I am trying to edit it for the
following:

Gaps sheet is where account and meter info is kept, along with periods
(months) that have already been reported.

Main sheet is where new data is kept. Want to be able to delete the rows
where the periods (months) have already been reported.

Gaps!A3:A10 = account #
Gaps!C3:C10 = meter #
Gaps!E3:E10 = reported periods
main sheet, cell A2 = account #
main sheet, cell F2 = meter #
main sheet, cell P2 = period that this billing data is for

=INDEX(Gaps!$E$3:$P$10,MATCH(1,(Gaps!$A$3:$A$10=A2 )*(Gaps!$D$3:$D$10=F2),0))

Should I be using a different function or are my operators incorrect? What
am I missing? HELP!!!

I forgot to add:

I would like to mark rows that should be deleted with "Yes" (must be
deleted) or "No" (stays).

Thanks again for any help!