Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 17th 12, 04:08 AM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 8
Default 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   Report Post  
Old September 17th 12, 08:04 AM
Senior Member
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 663
Default

Quote:
Originally Posted by Jenniferpod View Post
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   Report Post  
Old September 17th 12, 07:06 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 8
Default

Quote:
Originally Posted by Spencer101 View Post
=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   Report Post  
Old September 17th 12, 07:17 PM
Senior Member
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 663
Default

Quote:
Originally Posted by Jenniferpod View Post
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   Report Post  
Old September 17th 12, 07:20 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 8
Default

Quote:
Originally Posted by Jenniferpod View Post
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   Report Post  
Old September 17th 12, 07:40 PM
Senior Member
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 663
Default

Quote:
Originally Posted by Jenniferpod View Post
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
File Type: zip Jenniferpod Example.zip (5.9 KB, 60 views)
  #7   Report Post  
Old September 19th 12, 11:28 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 8
Default

Quote:
Originally Posted by Spencer101 View Post
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   Report Post  
Old September 20th 12, 08:20 AM
Senior Member
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 663
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If find function to not find anything Carlos Excel Programming 4 February 14th 08 09:25 AM
Help with Find function Extropian Excel Programming 3 May 30th 07 04:14 PM
Find function Kris Excel Programming 2 September 27th 06 10:00 AM
Find function Jahunga Excel Worksheet Functions 2 November 22nd 04 03:38 PM
backwards find function to find character in a string of text Ashleigh K. Excel Programming 1 January 14th 04 04:36 PM


All times are GMT +1. The time now is 07:15 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017