Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|