Excel Issue

[Miki]

I want to search a specific data in column B and retrieve the data in Column
A.

Is there any way out for this or do I have to interchange the columns only!!!!

I hope somebody can help me out ??

 

[Max]

Try index/match, it's more versatile in that it allows you to index (ie
return) any col to the right or left of the match col

Eg something like this in Sheet1's B1:
=INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0))
will return Sheet 2's col A with matching on Sheet2's col B
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik

 

[Miki]

It's working fine Max, but is there any other way to do this ????

 

[Max]

It's working fine Max,
Good to hear that

.. but is there any other way to do this ????

Since index/match gives you the flexibility mentioned earlier, which
addresses the intent underlying your original posting, what more could you
possibly be after, I wonder?

You could copy the top cell formula down to do it correspondingly for all
values. And you could use an IF(ISNA(..) error trap to return neat looking
blanks: "" for any unmatched cases, like this:
=IF(ISNA(MATCH(A1,Sheet2!B:B,0)),"",INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0)))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---

 

[Miki]

THANKS A TON MAX !!!!

Good to hear that

Since index/match gives you the flexibility mentioned earlier, which
addresses the intent underlying your original posting, what more could you
possibly be after, I wonder?

You could copy the top cell formula down to do it correspondingly for all
values. And you could use an IF(ISNA(..) error trap to return neat looking
blanks: "" for any unmatched cases, like this:
=IF(ISNA(MATCH(A1,Sheet2!B:B,0)),"",INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0)))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---

 

[Max]

Welcome. Do take a moment to press the "Yes" button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik