Which formula to use?
 

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?

Which formula to use?
 

Try this array formula** :

Sheet1:

A1 = name
B1 = price
C1 = color

Sheet2:

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

=INDEX(Sheet2!D:D,MATCH(1,(Sheet2!A1:A20=A1)*(Shee t2!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.

--
Biff
Microsoft Excel MVP


"Nicole" wrote in message
...
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?

Which formula to use?
 

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.
(Nicole)

"T. Valko" wrote:

Try this array formula** :

Sheet1:

A1 = name
B1 = price
C1 = color

Sheet2:

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

=INDEX(Sheet2!D:D,MATCH(1,(Sheet2!A1:A20=A1)*(Shee t2!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.

--
Biff
Microsoft Excel MVP


"Nicole" wrote in message
...
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?

Which formula to use?
 

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



"T. Valko" wrote:

Try this array formula** :

Sheet1:

A1 = name
B1 = price
C1 = color

Sheet2:

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

=INDEX(Sheet2!D:D,MATCH(1,(Sheet2!A1:A20=A1)*(Shee t2!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.

--
Biff
Microsoft Excel MVP


"Nicole" wrote in message
...
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?

Which formula to use?
 

Here's a small sample file that demonstrates this.

xNicole.xls 14kb

http://cjoint.com/?ghhIVUEtS3

--
Biff
Microsoft Excel MVP


"Nicole" wrote in message
...
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.
(Nicole)

"T. Valko" wrote:

Try this array formula** :

Sheet1:

A1 = name
B1 = price
C1 = color

Sheet2:

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

=INDEX(Sheet2!D:D,MATCH(1,(Sheet2!A1:A20=A1)*(Shee t2!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.

--
Biff
Microsoft Excel MVP


"Nicole" wrote in message
...
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?