Matching Data
Posted by Gene Tran on July 10, 2001 9:48 AM
I have two work sheet: ws1 is has SalespersonName and SalespersonID; ws2 has SalespersonName and CusterName. I want to Match up the SalespersonName and the SalespersonId to the CustomerName in ws2. I guess i Just want to write a formula so that all i have to do is insert a column in ws2 and then it'll automatically look in ws1 and match up the SalespersonID to the CustomerName. This is the formula that I'm trying to use but i can't seem to get it to work.
=IF(ISNUMBER(MATCH(A4,ws1!A2:A3,0)),INDEX(ws2!A2:A3,MATCH(VLOOKUP(A4,ws1!A2:B3,2,0),ws2!B2:B3,0)),"")
I guess it's because where it says ws1. How do i get it to look in that work sheet or link it.
Posted by Aladin Akyurek on July 10, 2001 10:46 AM
=IF(ISNUMBER(MATCH(A4,ws1!$A$2:$A$3,0),"",VLOOKUP(A4,ws1!$A$2:$A$3,2,0))
This formula will be in B4 on ws2.
Aladin
Posted by Aladin Akyurek on July 10, 2001 11:27 AM
Correction
Gene,
Switch "" and VLOOKUP parts in the formula I suggested: that is, if the MATCH result is a number, then do VLOOKUP, otherwise/else "".
Sorry about that.
