Creating a Unique Reference from 2 cells

[Christina Byrne]

I need to pull data from a 2nd spreadsheet with vlookup where my reference is
not unique (there is a non unique numerical reference and a non unique date
but combined these would be unique). I would like to combine a 3 digit number
and a date to create a unique reference e.g. '100' and '01/07/2008'.
Is it possible to do this?
Or how else can I pull data from one spreadsheet to another when the
numerical references are not unique?

 

[Bob Phillips]

=INDEX(Sheet2!A:A,MATCH(1,(Sheet2!B2:B200=100)*(Sheet2!C2:C200=--"2008-07-02"),0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Christina Byrne]

I need to create multiple unique references eg;
Spreadsheet 1
A B C
100 01/07/2008 LONDON
457 26/12/2001 BIRMINGHAM
001 15/01/1999 GLASGOW
Spreadsheet 2
001 04/02/1997 ORANGE
001 15/01/1999 BLUE
352 25/12/2010 YELLOW

Can I match the combination of number 001 and date of 15/01/1999 and pull
back 'BLUE' into my first spreadsheet to give the result
001 15/01/1999 GLASGOW BLUE

 

[Bob Phillips]

Sure,

=INDEX(Sheet2!C1:C2000,MATCH(1,(Sheet2!A1:A2000=A3)*(Sheet2!B1:B2000=B3),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)