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

[Guest]

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'!D1350 is match the data in column
'ws2!AA1:AA350 with the column 'ws1'!AB1:AB100. IF a match is found, then
populate WS2!x 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.

 

[Guest]

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

 

[Guest]

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]

welcome, KSL.