Is VLOOKUP the right function?
 

Hello,

I am working on a worksheet with 3 columns. A with dates, B with invoice
numbers, C with names.

I'd like to put a function in cell D1 that looks up a specific date in
column A and a specific invoice in column B and returns the name in column C.
Note that although there are several identical entries in columns A and B,
there is only one matching combination. (Although it would be a good idea to
get an error warning if there are 2 or more matching combinations).

Also is there a way (copy-paste or other) to get only unique entries from a
column (that is to exclude repeated entries)?

Any help apprieciated,

Thank you.

One way

=INDEX(C1:C200,MATCH(1,(A1:A200=DATE(2004,12,10))* (B1:B200=invoice),0))

entered with ctrl + shift & enter

so if your invoice was 1234

=INDEX(C1:C200,MATCH(1,(A1:A200=DATE(2004,12,10))* (B1:B200=1234),0))

I would personally use 2 more cells and put the criteria there, assume we
use E1 for the date and F1 for the invoice

=INDEX(C1:C200,MATCH(1,(A1:A200=E1)*(B1:B200=F1),0 ))

also array entered, that way you don't have to edit the formula when you
change the criteria, just change the cell contents of E1 and F1

Regards,

Peo Sjoblom

"bay" wrote:

Hello,

I am working on a worksheet with 3 columns. A with dates, B with invoice
numbers, C with names.

I'd like to put a function in cell D1 that looks up a specific date in
column A and a specific invoice in column B and returns the name in column C.
Note that although there are several identical entries in columns A and B,
there is only one matching combination. (Although it would be a good idea to
get an error warning if there are 2 or more matching combinations).

Also is there a way (copy-paste or other) to get only unique entries from a
column (that is to exclude repeated entries)?

Any help apprieciated,

Thank you.

I get a #N/A error and i dont know why...

thank you though.... is there another way... anyone?

S.

"Peo Sjoblom" wrote:

One way

=INDEX(C1:C200,MATCH(1,(A1:A200=DATE(2004,12,10))* (B1:B200=invoice),0))

entered with ctrl + shift & enter

so if your invoice was 1234

=INDEX(C1:C200,MATCH(1,(A1:A200=DATE(2004,12,10))* (B1:B200=1234),0))

I would personally use 2 more cells and put the criteria there, assume we
use E1 for the date and F1 for the invoice

=INDEX(C1:C200,MATCH(1,(A1:A200=E1)*(B1:B200=F1),0 ))

also array entered, that way you don't have to edit the formula when you
change the criteria, just change the cell contents of E1 and F1

Regards,

Peo Sjoblom

"bay" wrote:

Hello,

I am working on a worksheet with 3 columns. A with dates, B with invoice
numbers, C with names.

I'd like to put a function in cell D1 that looks up a specific date in
column A and a specific invoice in column B and returns the name in column C.
Note that although there are several identical entries in columns A and B,
there is only one matching combination. (Although it would be a good idea to
get an error warning if there are 2 or more matching combinations).

Also is there a way (copy-paste or other) to get only unique entries from a
column (that is to exclude repeated entries)?

Any help apprieciated,

Thank you.

The reason you get an error is either that you didn't enter the formula with
ctrl + shift & enter or that the criteria you are using is not found in the
lookups, that might be something silly like an extra space or something. The
only alternatives to an array
formula is either filtering or creating an extra column where you
concatenate the 2
columns and also concatenate the 2 lookup values, since you are using dates
which would come out as 5 digit numbers I wouldn't recommend the latter

Regards,

Peo Sjoblom

"bay" wrote:

I get a #N/A error and i dont know why...

thank you though.... is there another way... anyone?

S.

"Peo Sjoblom" wrote:

One way

=INDEX(C1:C200,MATCH(1,(A1:A200=DATE(2004,12,10))* (B1:B200=invoice),0))

entered with ctrl + shift & enter

so if your invoice was 1234

=INDEX(C1:C200,MATCH(1,(A1:A200=DATE(2004,12,10))* (B1:B200=1234),0))

I would personally use 2 more cells and put the criteria there, assume we
use E1 for the date and F1 for the invoice

=INDEX(C1:C200,MATCH(1,(A1:A200=E1)*(B1:B200=F1),0 ))

also array entered, that way you don't have to edit the formula when you
change the criteria, just change the cell contents of E1 and F1

Regards,

Peo Sjoblom

"bay" wrote:

Hello,

I am working on a worksheet with 3 columns. A with dates, B with invoice
numbers, C with names.

I'd like to put a function in cell D1 that looks up a specific date in
column A and a specific invoice in column B and returns the name in column C.
Note that although there are several identical entries in columns A and B,
there is only one matching combination. (Although it would be a good idea to
get an error warning if there are 2 or more matching combinations).

Also is there a way (copy-paste or other) to get only unique entries from a
column (that is to exclude repeated entries)?

Any help apprieciated,

Thank you.