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.

=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

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.

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.

Excellent! Thank you.

Not a problem. Glad I could help. :)