Remember Me?

#1
September 17th 12, 04:08 AM
 Junior Member First recorded activity by ExcelBanter: Sep 2012 Posts: 8
Find function

I have two worksheets. In cell a2 of worksheet 1 I want to find if the value of worksheet 1 cell b2 exists within worksheet 2 column b. If found, I want to return the value of worksheet 2 column a (same row that the the original lookup was found). If not found, I want to leave the cell blank.

#2
September 17th 12, 08:04 AM
 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 663

Quote:
 Originally Posted by Jenniferpod I have two worksheets. In cell a2 of worksheet 1 I want to find if the value of worksheet 1 cell b2 exists within worksheet 2 column b. If found, I want to return the value of worksheet 2 column a (same row that the the original lookup was found). If not found, I want to leave the cell blank.
=IFERROR(VLOOKUP(A2,CHOOSE({1,2},SHEET2!B:B,SHEET2 !A:A),2,FALSE),"")

This should do what you need if you're using Excel 2007 or later.
#3
September 17th 12, 07:06 PM
 Junior Member First recorded activity by ExcelBanter: Sep 2012 Posts: 8

Quote:
 Originally Posted by Spencer101 =IFERROR(VLOOKUP(A2,CHOOSE({1,2},SHEET2!B:B,SHEET2 !A:A),2,FALSE),"") This should do what you need if you're using Excel 2007 or later.
It keeps saying I have a circular reference
#4
September 17th 12, 07:17 PM
 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 663

Quote:
 Originally Posted by Jenniferpod It keeps saying I have a circular reference
That formula goes in say cell B2 on Sheet1 rather than on Sheet2.

If that fails, post an example workbook and I'll show you how it goes.
#5
September 17th 12, 07:20 PM
 Junior Member First recorded activity by ExcelBanter: Sep 2012 Posts: 8

Quote:
 Originally Posted by Jenniferpod It keeps saying I have a circular reference
Maybe if I just put it on one worksheet now until I understand how the function is working. So say I have four columns.... Column B and Column D have the same information in them but all jumbled. I want to find B1 within Column D. Then I want to bring the value of Column C next to the located D and bring it back to cell A1. I've tried to do a little chart to show you.

Purple Apple Blue Banana
Blue Banana Green Orange
Red Grape Purple Apple
Green Orange Red Grape

#6
September 17th 12, 07:40 PM
 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 663

Quote:
 Originally Posted by Jenniferpod Maybe if I just put it on one worksheet now until I understand how the function is working. So say I have four columns.... Column B and Column D have the same information in them but all jumbled. I want to find B1 within Column D. Then I want to bring the value of Column C next to the located D and bring it back to cell A1. I've tried to do a little chart to show you. Purple Apple Blue Banana Blue Banana Green Orange Red Grape Purple Apple Green Orange Red Grape

Have a look at the attachment. Does this clear things up?

It would be a case of just a straightforward VLOOKUP if your C and D columns were the other way round, but as they're not I've used CHOOSE to do a "left lookup".

Let me know if you need any of it clarified.
Attached Files
 Jenniferpod Example.zip (5.9 KB, 61 views)
#7
September 19th 12, 11:28 PM
 Junior Member First recorded activity by ExcelBanter: Sep 2012 Posts: 8

Quote:
 Originally Posted by Spencer101 Have a look at the attachment. Does this clear things up? It would be a case of just a straightforward VLOOKUP if your C and D columns were the other way round, but as they're not I've used CHOOSE to do a "left lookup". Let me know if you need any of it clarified.
Excellent! Thank you.
#8
September 20th 12, 08:20 AM
 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 663

Quote:
 Originally Posted by Jenniferpod Excellent! Thank you.
Not a problem. Glad I could help.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Carlos Excel Programming 4 February 14th 08 09:25 AM Extropian Excel Programming 3 May 30th 07 04:14 PM Kris Excel Programming 2 September 27th 06 10:00 AM Jahunga Excel Worksheet Functions 2 November 22nd 04 03:38 PM Ashleigh K. Excel Programming 1 January 14th 04 04:36 PM

All times are GMT +1. The time now is 04:58 AM.