Home |
Search |
Today's Posts |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Thanks Spencer
Your formula works in theory but when I input the first and last names the 2 cells which should populate with phone number and email remain blank. Example can be downloaded from http://www.keepthefaith.org.uk/Test.xlsx Andy "Spencer101" wrote in message ... 'Andy Roberts[_3_ Wrote: ;1602842']I have a spreadsheet with 2 workbooks called clients and jobs. In the clients workbook I have 4 columns which include firstname, lastname, telephone and email. In the second worksheet (Jobs) I have several columns but 4 are the same as the ones above the difference being that in the telephone and email columns I have a formula which populates these cells from the client worksheet using the first and last name cells. The formula I use (for the telephone column) is :- =INDEX(Clients!$C$3:$C$1000,MATCH($K1&$L1,Clients! $A$3:$A$1000&Clients!$B$3:$B$1000,0)) This works fine except I get a #N/A error which I haven't completed the firstname and lastname cells. I amended the formula to the following to "hide" the error, which it does, but also leaves the cells blank after I've added firstname and lastname. =IF(ISERROR((INDEX(Clients!$C$3:$C$1000,MATCH($K1& $L1,Clients!$A$3:$A$1000&Clients!$B$3:$B$1000,0))) ),"",(INDEX(Clients!$C$3:$C$1000,MATCH($K1&$L1,Cli ents!$A$3:$A$1000&Clients!$B$3:$B$1000,0)))) What am I doing wrong? Office 2010 Win XP Does this do what you need? Hard to tell without an example workbook. *=IFERROR(INDEX(Clients!$C$3:$C$1000,MATCH($K2&$L2 ,Clients!$A$3:$A$1000&Clients!$B$3:$B$1000,0)),"") * -- Spencer101 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Index Match Problem | Excel Worksheet Functions | |||
Index Match Problem | Excel Worksheet Functions | |||
Index/Match problem | Excel Worksheet Functions | |||
Index Match Problem | Excel Worksheet Functions | |||
Array Formula Index Match formulas in VBA problem | Excel Programming |