Lookup?

[alip43]

Can anyone help with the following problem please?

I have created an excel worksheet from data exported from our management system which contains the following columns
Name Surname Gender Year Reg etc
I then have another blank worksheet in the same workbook that contains similar columns.

The user wants to be able to type in the Name and Surname column on the blank worksheet and have it populated by the correct information from the worksheet I have created from our management system.

Hope I have explained this properly - many thanks in advance.

 

[SkipVought]

Hi,

In your source data, add a column that concatenates the Name & Surname, for the lookup value.

In the user sheet, assuming that the user enters Name & surname in columns A & B, make column C, for instance, contain the concatenated key.

Then each column following can have a formula like this to return a specific column's data, assuming that the user's data is in row 2...
[tt]
=INDEX(YourSourceTableReference,MATCH(C2,YourNEW_KEY_Reference,0),TheColumnNUmberYouWantToReturn)
[/tt]
where YourSourceTableReference might be something like
[tt]
Sheet2!$A$2:$Z$999
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[alip43]

Thanks Skip
That works beautifully.
Obviously when I copy down the formulas I get #N/A errors as there is no information in B:B (forename) or C:C (surname)yet. I would like these cells to appear blank until I add a name.
I have tried to use conditional formatting, eg
selecting all the cells and using Conditional Formatting - Formula is =ISERROR(D) and formatting the text to white
but nothing seems to happen (D is where my concatenated name appears when I have entered a name into B:B and C:C).
Can you help as to where I am going wrong?

 

[SkipVought]

If you use Data > List - Create List, your formula(s) can be dynamically ADDED, when your enter data on the next available row, if they are in column(s) directly adjacent and to the right of your data.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}