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

L

Leonhardtk

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

Max

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
 
L

Leonhardtk

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top