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?
|