Retrieving a field from another worksheet - with conditions...
 

I have two worksheets. In worksheet 1, I have several columns. In worksheet
2 I have other data. I have a column that has the same information (but not
necessarily on the same row. These columns are 'ws1'!AB1:AB100 in the first
worksheet and 'ws2'!AA1:AA350 in the second worksheet.
What I need to do, in 'ws2'!D1:D350 is match the data in column
'ws2!AA1:AA350 with the column 'ws1'!AB1:AB100. IF a match is found, then
populate WS2!:Dx with the value found in ws1!Bx. "x" is the row that has the
match.
Of note, In worksheet 1, the value in AB1:AB100 is unique. In worksheet 2,
the value can be repeated several times.

Here's what I have (sofar) in 'WS2'!D2:
=IF(AA2="","", IF(ISNA(MATCH(AA2, 'ws1'!$AB$2:$AB$100,0)),"",'ws1'!B2:B100))

This almost works, but appears to return the value from the row on which the
' internal loop' is processing and not where the match actually occurs.
I want the value in WS1 column Bx where x is the row number where the match
occured.

Appreciate any help !

KSL.

Retrieving a field from another worksheet - with conditions...
 

In Sheet2,

Try instead in D2:
=IF(ISNA(MATCH(AA2,Sheet1!AB:AB,0)),"",
INDEX(Sheet1!B:B,MATCH(AA2,Sheet1!AB:AB,0)))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Leonhardtk" wrote:
I have two worksheets. In worksheet 1, I have several columns. In worksheet
2 I have other data. I have a column that has the same information (but not
necessarily on the same row. These columns are 'ws1'!AB1:AB100 in the first
worksheet and 'ws2'!AA1:AA350 in the second worksheet.
What I need to do, in 'ws2'!D1:D350 is match the data in column
'ws2!AA1:AA350 with the column 'ws1'!AB1:AB100. IF a match is found, then
populate WS2!:Dx with the value found in ws1!Bx. "x" is the row that has the
match.
Of note, In worksheet 1, the value in AB1:AB100 is unique. In worksheet 2,
the value can be repeated several times.

Here's what I have (sofar) in 'WS2'!D2:
=IF(AA2="","", IF(ISNA(MATCH(AA2, 'ws1'!$AB$2:$AB$100,0)),"",'ws1'!B2:B100))

This almost works, but appears to return the value from the row on which the
' internal loop' is processing and not where the match actually occurs.
I want the value in WS1 column Bx where x is the row number where the match
occured.

Appreciate any help !

KSL.

Retrieving a field from another worksheet - with conditions...
 

Works Perfectly! I had a little trouble at first, but it was my typo (I my
spreadsheet is on another network!).

Thanks a-million!

KSL

"Max" wrote:

In Sheet2,

Try instead in D2:
=IF(ISNA(MATCH(AA2,Sheet1!AB:AB,0)),"",
INDEX(Sheet1!B:B,MATCH(AA2,Sheet1!AB:AB,0)))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Leonhardtk" wrote:
I have two worksheets. In worksheet 1, I have several columns. In worksheet
2 I have other data. I have a column that has the same information (but not
necessarily on the same row. These columns are 'ws1'!AB1:AB100 in the first
worksheet and 'ws2'!AA1:AA350 in the second worksheet.
What I need to do, in 'ws2'!D1:D350 is match the data in column
'ws2!AA1:AA350 with the column 'ws1'!AB1:AB100. IF a match is found, then
populate WS2!:Dx with the value found in ws1!Bx. "x" is the row that has the
match.
Of note, In worksheet 1, the value in AB1:AB100 is unique. In worksheet 2,
the value can be repeated several times.

Here's what I have (sofar) in 'WS2'!D2:
=IF(AA2="","", IF(ISNA(MATCH(AA2, 'ws1'!$AB$2:$AB$100,0)),"",'ws1'!B2:B100))

This almost works, but appears to return the value from the row on which the
' internal loop' is processing and not where the match actually occurs.
I want the value in WS1 column Bx where x is the row number where the match
occured.

Appreciate any help !

KSL.

Retrieving a field from another worksheet - with conditions...
 

welcome, KSL.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Leonhardtk" wrote in message
...
Works Perfectly! I had a little trouble at first, but it was my typo (I
my
spreadsheet is on another network!).

Thanks a-million!

KSL