Matching columns on seperate sheets

[HowardC]

I have a heading called asset type in column A and beneath this I have the various asset types for eg Corporate signage, Furniture and Fittings etc, In col H I have a heading called Departments and below this the varios departments for eg Administration, Parts etc and in Column P I have a heading Profit Code. These are all on sheet 1

On sheet 3, I have the Asset Type Dept Code Split

Under asset type I will have for eg Corporate signage
under Dept , I will have for Eg Administration
under code I will have for eg 191475
Under Split i will have for eg 100

I need a code in column P on sheeet 1 that where the asset type and department on sheet 1 and 3 match, that the code and
for Eg Sheet 1 where the asset type is say Corporate signage and the department is Administration and it matches the asset type & department on sheet 3, the under profit code in Col P , the following must appear 191475= 100

Presently I am using a linking formula, but it is time consuming



I have attached a sample workbook


Your assistance in this regard wil be most appreciated

[iansavell]

The attached updated workbook does two things:

1. Concatenates the asset type and department names to give a single lookup value
2. Uses vlookup() to retrieve the values for the profit code from sheet 3

Is that what you want? Use Excel help to find out more about vlookup(). You can hide the columns with the concatenated codes.

Note that one code doesn't exist in sheet 3 so the lookup returns #NA. You can instead insert a message or default value by extending the vlookup to a formula like

=if(isna(match(value,table,false)),"default",vlook up(value,table,column,false))

so if there is no matching value in the lookup the cell shows "default" (or whatever you want to put in there)

Ian.

[HowardC]

Hi Ian

Thanks for the help, much appreciated.This is exactly what I am looking for.Very clever to add the additional column in order to calculate the Vlookup. Excel is such a powerful tool. I've learnt more from these forums than from Excel books, although the books have given me a good grounding

Regards

Howard