Vlookup(?) with 2 Lookup Values
 

I hope I can explain this ok:
My vendor reports an Account ID listed multiple times, once for each service
subscribed to. My billing is based on the two items: some services are
billed under one sales account, some on another.
I currently have my sheet set up where it is sorted by service and I vlookup
the account ID for each service seperately (using exact cell locations
instead of columns).
Is there a formula so that it looks at the account id, then the service, and
returns the sales account?
I hope that was clear - thanks in advance for your help!

Vlookup(?) with 2 Lookup Values
 

=SUMPRODUCT(--(A1:A100="account id"),--(B1:B100="service"),C1:C100)
Adjust to suit


"o1darcie1o" wrote:

I hope I can explain this ok:
My vendor reports an Account ID listed multiple times, once for each service
subscribed to. My billing is based on the two items: some services are
billed under one sales account, some on another.
I currently have my sheet set up where it is sorted by service and I vlookup
the account ID for each service seperately (using exact cell locations
instead of columns).
Is there a formula so that it looks at the account id, then the service, and
returns the sales account?
I hope that was clear - thanks in advance for your help!

Vlookup(?) with 2 Lookup Values
 

I've come across this problem too. There may be a better solution, but
mine was to concatenate the two values into one cell and then use
VLOOKUP to compare. So the formula would look like this:

=AccountID&ServiceRef

That creates a unique identifier across both dimensions. Seems to work
just fine.

o1darcie1o wrote:
I hope I can explain this ok:
My vendor reports an Account ID listed multiple times, once for each service
subscribed to. My billing is based on the two items: some services are
billed under one sales account, some on another.
I currently have my sheet set up where it is sorted by service and I vlookup
the account ID for each service seperately (using exact cell locations
instead of columns).
Is there a formula so that it looks at the account id, then the service, and
returns the sales account?
I hope that was clear - thanks in advance for your help!

Vlookup(?) with 2 Lookup Values
 

I'm sorry - I don't know how to adjust that to suit! I'm not all that good
with formulas.
In workbook1, I have acct id(a), service(b), sales account(c).
In workbook2, I have acct id(a), service(b), and i need to pull the sales
account in, but only if acct id & service in wkbk1 match acct id & service in
wkbk2.



"Teethless mama" wrote:

=SUMPRODUCT(--(A1:A100="account id"),--(B1:B100="service"),C1:C100)
Adjust to suit


"o1darcie1o" wrote:

I hope I can explain this ok:
My vendor reports an Account ID listed multiple times, once for each service
subscribed to. My billing is based on the two items: some services are
billed under one sales account, some on another.
I currently have my sheet set up where it is sorted by service and I vlookup
the account ID for each service seperately (using exact cell locations
instead of columns).
Is there a formula so that it looks at the account id, then the service, and
returns the sales account?
I hope that was clear - thanks in advance for your help!

Vlookup(?) with 2 Lookup Values
 

When I don't care too much, I usually just cheat and have an extra
column with an index value. ie. I'll insert a column in front of the
values, and put the formula =B2&C2 (assuming the account and service
are in those two columns).

Then I'll run the VLOOKUP based on that column, ie.

=VLOOKUP(A1&B1,'Sheet1!$A$2:$D$1000,3,FALSE)

Scott

o1darcie1o wrote:
I hope I can explain this ok:
My vendor reports an Account ID listed multiple times, once for each service
subscribed to. My billing is based on the two items: some services are
billed under one sales account, some on another.
I currently have my sheet set up where it is sorted by service and I vlookup
the account ID for each service seperately (using exact cell locations
instead of columns).
Is there a formula so that it looks at the account id, then the service, and
returns the sales account?
I hope that was clear - thanks in advance for your help!

Vlookup(?) with 2 Lookup Values
 

Thanks guys - that's what I came up with, too, but not until after I posted.
Thank you both!!!

"Scott" wrote:

When I don't care too much, I usually just cheat and have an extra
column with an index value. ie. I'll insert a column in front of the
values, and put the formula =B2&C2 (assuming the account and service
are in those two columns).

Then I'll run the VLOOKUP based on that column, ie.

=VLOOKUP(A1&B1,'Sheet1!$A$2:$D$1000,3,FALSE)

Scott

o1darcie1o wrote:
I hope I can explain this ok:
My vendor reports an Account ID listed multiple times, once for each service
subscribed to. My billing is based on the two items: some services are
billed under one sales account, some on another.
I currently have my sheet set up where it is sorted by service and I vlookup
the account ID for each service seperately (using exact cell locations
instead of columns).
Is there a formula so that it looks at the account id, then the service, and
returns the sales account?
I hope that was clear - thanks in advance for your help!

Vlookup(?) with 2 Lookup Values
 

The concatination of two cells to make a unique key is generally ok if and
only if the first column contains fixed width values.

If they don't then there could be a concern with duplicate keys, take for
example

a1="A11" b1="1" a1&b1="A111"
a2="A1 b2="11" a2&b2="A111"

it is better to put a seperator (that does not occur in the two cells)
between the cells as in

a1="A11" b1="1" a1&":"&b1="A11:1"
a2="A1 b2="11" a2&":"&b2="A1:11"

Good examples of seperators are ":", "|", "(@)".

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"o1darcie1o" wrote:

Thanks guys - that's what I came up with, too, but not until after I posted.
Thank you both!!!

"Scott" wrote:

When I don't care too much, I usually just cheat and have an extra
column with an index value. ie. I'll insert a column in front of the
values, and put the formula =B2&C2 (assuming the account and service
are in those two columns).

Then I'll run the VLOOKUP based on that column, ie.

=VLOOKUP(A1&B1,'Sheet1!$A$2:$D$1000,3,FALSE)

Scott

o1darcie1o wrote:
I hope I can explain this ok:
My vendor reports an Account ID listed multiple times, once for each service
subscribed to. My billing is based on the two items: some services are
billed under one sales account, some on another.
I currently have my sheet set up where it is sorted by service and I vlookup
the account ID for each service seperately (using exact cell locations
instead of columns).
Is there a formula so that it looks at the account id, then the service, and
returns the sales account?
I hope that was clear - thanks in advance for your help!