Creating a Unique Reference from 2 cells
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? |
Creating a Unique Reference from 2 cells
=INDEX(Sheet2!A:A,MATCH(1,(Sheet2!B2:B200=100)*(Sh eet2!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" <Christina wrote in message ... 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? |
Creating a Unique Reference from 2 cells to use vlook up
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" wrote: =INDEX(Sheet2!A:A,MATCH(1,(Sheet2!B2:B200=100)*(Sh eet2!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" <Christina wrote in message ... 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? |
Creating a Unique Reference from 2 cells to use vlook up
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) "Christina Byrne" wrote in message ... 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" wrote: =INDEX(Sheet2!A:A,MATCH(1,(Sheet2!B2:B200=100)*(Sh eet2!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" <Christina wrote in message ... 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? |
All times are GMT +1. The time now is 10:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com