Which formula to use?

[Nicole]

Hi,
I am trying to lookup & match a table of data from 2 different sheets.
(meaning if sheet 1-Name,price, colour is equal to sheet 2-name,price,colour)
Once the data matches, it should return a reference id to me. See below
example:
Sheet 1
Name Price Colour

Sheet 2
Name Price Colour Reference id

Can someone advise what formula I should use?

 

[T. Valko]

Try this array formula** :

Sheet1:

A1 = name
B1 = price
C1 = color

Sheet2:

A1:A20 = names
B1:B20 = prices
C1:C20 = colors
D120 = IDs

=INDEX(Sheet2!D,MATCH(1,(Sheet2!A1:A20=A1)*(Sheet2!B1:B20=B1)*(Sheet2!C1:C20=C1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Nicole]

Hi Valko,
I tried the formula you suggested but I got a #N/A error.
Are you able to email me a templated excel of what you explained below?

Thanks.
(e-mail address removed) (Nicole)

 

[Nicole]

Hi
I tried but received a #N/A error.

Try this array formula** :

Sheet1:

A1 = name
B1 = price
C1 = color

Sheet2:

A1:A20 = names
B1:B20 = prices
C1:C20 = colors
D120 = IDs

=INDEX(Sheet2!D,MATCH(1,(Sheet2!A1:A20=A1)*(Sheet2!B1:B20=B1)*(Sheet2!C1:C20=C1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

Here's a small sample file that demonstrates this.

xNicole.xls 14kb

http://cjoint.com/?ghhIVUEtS3