Excel help

[Siouxsie]

My knowledge of excel is fairly basic so I'm hoping someone can help me.

I have 2 spreadsheets both containing lists of the same items.
The description of these items is 3 words eg:

A1 = blue, B1 = coat, C1 = large
A2 = green, B2 = jumper, C2 = medium
A3 = green, B3 = coat, C2 = small

Spreadsheet 2 also has a price in column D


The items are in different orders on each spreadsheet, in fact on spreadsheet 1 they are split over a number of workbooks, whereas on 2 they are in one long list.

What I need to do is find the matching item on spreadsheet 2 and drop the price into column D in spreadsheet 1.

So what formula do I need to put into column D on 1 so that it retrieves the correct price from 2?

All help gratefully received.

 

[Shents]

Sounds like a vlookup to me. If you use the insert function option to help you write the formula.

Sumif is also a useful formular to work out the total of items matching the same criteria

 

[Darth Vodka]

in column D of your target worksheet (incidentally a workbook is a file and a worksheet is a tab within a file)

write a formula that concatenates the three fields into a unique reference (a primary key if you will) like:-

=A1&B1&C1

and do something similar in your source sheet for the next free column, E

=A1&B1&C1

now it's a case of a lookup formula in your target next clear column (E)

=INDEX(source!$D$1:$D$3,MATCH(D1,source!$E$1:$E$3,))

you can also use VLOOKUP instead of match/index and there are ways of doing it with concatenating anything...

 

[Siouxsie]

Whoops I did mean worksheet :icon_redf

Thank you both for your help. I had tried with VLOOKUP but couldn't get past the problem of having 3 fields.

 

[Darth Vodka]

Whoops I did mean worksheet :icon_redf

Thank you both for your help. I had tried with VLOOKUP but couldn't get past the problem of having 3 fields.

thought you did

in trying to make a unique (primary key) field, you may want to add a delimiting character

like =a1&"|"&b1&"|"&c1

just in case your fields may line up to form duplicates

eg "12" & "34" = "1234", but so does "1" & "234"

 

[Siouxsie]

Thanks