Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help - Simple Formula !
Hi Forum , I have two worksheets . Worksheet ,NewList has two columns , FirstName , LastName. Worksheet ,MasterList has 3 columns, FirstName, LastName, Properties. I want to add a third column ,Properties to worksheet ,NewList such that it compares FirstName and LastName from MasterList and returns the corresponding Properties value from MasterList. The most important constraint here is that FirstName,LastName value should be on the same row . For e.g., FirstName(1),LastName(1) in NewList could be FirstName(6),LastName(6) in MasterList and I need to return Properties(6) from the MasterList into Properties(1) in NewList. The values in brackets above refer to the row number. Looks like this will involve , IF ,AND and MATCH functions together. Hope this is not confusing . I know this must have been asked and responded to by the experts several times. All the help will be very much appreciated. Thanks, Girish -- Girish Punjabi ------------------------------------------------------------------------ Girish Punjabi's Profile: http://www.excelforum.com/member.php...o&userid=16048 View this thread: http://www.excelforum.com/showthread...hreadid=387693 |
#2
|
|||
|
|||
i assumed your master list is in columns a-c. if you can put in a column d where d2 = a2&b2 and copied down. then =OFFSET(Sheet1!C1,MATCH(A2&B2,Sheet1!D2:D4,0),0) where my sheet1 is your master list sheet and my a2 and b2 are the names on newlist note i only did this for 3 rows (2:4 -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=387693 |
#3
|
|||
|
|||
Duane,
One exposure here is ambiguous first/last name combinations. Jons Mith and Jon Smith would be equivalent. Probably not a great example, but it's getting late. I'm trying. You could protect against that with: d2 = a2 & "*" & b2 =OFFSET(Sheet1!C1,MATCH(A2 & "*" & B2,Sheet1!D2:D4,0),0) Untested. The asterisk could be any character. -- Earl Kiosterud www.smokeylake.com "duane" wrote in message ... i assumed your master list is in columns a-c. if you can put in a column d where d2 = a2&b2 and copied down. then =OFFSET(Sheet1!C1,MATCH(A2&B2,Sheet1!D2:D4,0),0) where my sheet1 is your master list sheet and my a2 and b2 are the names on newlist note i only did this for 3 rows (2:4 -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=387693 |
#4
|
|||
|
|||
Ed, good suggestion....... -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=387693 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple formula help | Excel Worksheet Functions | |||
help with simple formula | Excel Worksheet Functions | |||
Excel Miscalculates simple formula..Help!! | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |