| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and return value
I have 2 worksheets, W1 has a unique id that I need to place on W2, if the
names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup and return value
On worksheet 2, cell b2:
=INDEX('Worksheet 1'!A:A,MATCH(A2,'Worksheet 1'!B:B,0)) With error trapping: =IF(ISNUMBER(MATCH(A2,'Worksheet 1'!B:B,0)),INDEX('Worksheet 1'!A:A,MATCH(A2,'Worksheet 1'!B:B,0)),"") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Krista" wrote: I have 2 worksheets, W1 has a unique id that I need to place on W2, if the names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup and return value
One other way, try this
Assuming your name start in A2 of Sheet 2, place this formula in B2 and copy down Take note that this formula requires that you sort your source data ascending for it to work. =LOOKUP(A2,Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5) Adjust the range to suit yours. -- Hope this help Please click the Yes button below if this post have helped in your needs Thank You cheers, francis "Krista" wrote: I have 2 worksheets, W1 has a unique id that I need to place on W2, if the names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup and return value
Thanks, so far this worked for what I needed.
"Francis" wrote: One other way, try this Assuming your name start in A2 of Sheet 2, place this formula in B2 and copy down Take note that this formula requires that you sort your source data ascending for it to work. =LOOKUP(A2,Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5) Adjust the range to suit yours. -- Hope this help Please click the Yes button below if this post have helped in your needs Thank You cheers, francis "Krista" wrote: I have 2 worksheets, W1 has a unique id that I need to place on W2, if the names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup and return value
Now I need to lookup two values, if they match return value.
Sheet 1 Cust Name Company Name ID Sheet 2 Cust Name Company Name I need to add the ID to Sheet 2 if the CustName & Company Name match give me ID. Can you help. "Francis" wrote: One other way, try this Assuming your name start in A2 of Sheet 2, place this formula in B2 and copy down Take note that this formula requires that you sort your source data ascending for it to work. =LOOKUP(A2,Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5) Adjust the range to suit yours. -- Hope this help Please click the Yes button below if this post have helped in your needs Thank You cheers, francis "Krista" wrote: I have 2 worksheets, W1 has a unique id that I need to place on W2, if the names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup and return value
I need to lookup two values in Sheet 1, if they match Sheet 2 then return
value. Sheet 1 Column A = CustName Column B = Company Name Column C = Id Sheet 2 Column A = custname Column B - Company name Column C = need to add ID Can you help. Thanks "Luke M" wrote: On worksheet 2, cell b2: =INDEX('Worksheet 1'!A:A,MATCH(A2,'Worksheet 1'!B:B,0)) With error trapping: =IF(ISNUMBER(MATCH(A2,'Worksheet 1'!B:B,0)),INDEX('Worksheet 1'!A:A,MATCH(A2,'Worksheet 1'!B:B,0)),"") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Krista" wrote: I have 2 worksheets, W1 has a unique id that I need to place on W2, if the names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup and return value
Hi,
Try this array formula (Ctrl+Shift+Enter) =index(sheet1!$A$2:$C$50,match(1,(sheet1!$A$2:$A$5 0=A2)*(sheet1!$B$2:$B$50=B2),0),3) A2 has the customer name on sheet2 B2 has the company name on sheet2 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Krista" wrote in message ... Now I need to lookup two values, if they match return value. Sheet 1 Cust Name Company Name ID Sheet 2 Cust Name Company Name I need to add the ID to Sheet 2 if the CustName & Company Name match give me ID. Can you help. "Francis" wrote: One other way, try this Assuming your name start in A2 of Sheet 2, place this formula in B2 and copy down Take note that this formula requires that you sort your source data ascending for it to work. =LOOKUP(A2,Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5) Adjust the range to suit yours. -- Hope this help Please click the Yes button below if this post have helped in your needs Thank You cheers, francis "Krista" wrote: I have 2 worksheets, W1 has a unique id that I need to place on W2, if the names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup and return value
Thanks for your support. Do you have any suggestions on Formula training.
"Ashish Mathur" wrote: Hi, Try this array formula (Ctrl+Shift+Enter) =index(sheet1!$A$2:$C$50,match(1,(sheet1!$A$2:$A$5 0=A2)*(sheet1!$B$2:$B$50=B2),0),3) A2 has the customer name on sheet2 B2 has the company name on sheet2 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Krista" wrote in message ... Now I need to lookup two values, if they match return value. Sheet 1 Cust Name Company Name ID Sheet 2 Cust Name Company Name I need to add the ID to Sheet 2 if the CustName & Company Name match give me ID. Can you help. "Francis" wrote: One other way, try this Assuming your name start in A2 of Sheet 2, place this formula in B2 and copy down Take note that this formula requires that you sort your source data ascending for it to work. =LOOKUP(A2,Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5) Adjust the range to suit yours. -- Hope this help Please click the Yes button below if this post have helped in your needs Thank You cheers, francis "Krista" wrote: I have 2 worksheets, W1 has a unique id that I need to place on W2, if the names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup and return value
You are welcome. For understanding formulas, refer to Excel's Help menu and
post questions in forums -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Krista" wrote in message ... Thanks for your support. Do you have any suggestions on Formula training. "Ashish Mathur" wrote: Hi, Try this array formula (Ctrl+Shift+Enter) =index(sheet1!$A$2:$C$50,match(1,(sheet1!$A$2:$A$5 0=A2)*(sheet1!$B$2:$B$50=B2),0),3) A2 has the customer name on sheet2 B2 has the company name on sheet2 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Krista" wrote in message ... Now I need to lookup two values, if they match return value. Sheet 1 Cust Name Company Name ID Sheet 2 Cust Name Company Name I need to add the ID to Sheet 2 if the CustName & Company Name match give me ID. Can you help. "Francis" wrote: One other way, try this Assuming your name start in A2 of Sheet 2, place this formula in B2 and copy down Take note that this formula requires that you sort your source data ascending for it to work. =LOOKUP(A2,Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5) Adjust the range to suit yours. -- Hope this help Please click the Yes button below if this post have helped in your needs Thank You cheers, francis "Krista" wrote: I have 2 worksheets, W1 has a unique id that I need to place on W2, if the names match. If name matches from W1, then place Unique ID on W2 Worksheet 1 - 2 columns Unique Id Name 100 Little 200 Smith 201 Blue 303 Yellow Worksheet 2 - 2 columns - Need to display Id Name ID Little Yellow Blue Smith |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup and return next value | Excel Worksheet Functions | |||
| lookup and return first... | Excel Worksheet Functions | |||
| LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
| Lookup and return | Excel Worksheet Functions | |||
| How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
Linear Mode
