Excel Issue
 

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 ??

Excel Issue
 

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" wrote:
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 ??

Excel Issue
 

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

"Max" wrote:

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" wrote:
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 ??

Excel Issue
 

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
---

Excel Issue
 

THANKS A TON MAX !!!!



"Max" wrote:

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
---

Excel Issue
 

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
---
"Miki" wrote:
THANKS A TON MAX !!!!