Index / Match Formula Problem

[Andy Roberts]

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,Clients!$A$3:$A$1000&Clients!$B$3:$B$1000,0))))

What am I doing wrong?

Office 2010
Win XP

 

[Vacuum Sealed]

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,Clients!$A$3:$A$1000&Clients!$B$3:$B$1000,0))))

What am I doing wrong?

Office 2010
Win XP

Hey Andy

If your Clients have a Client ID No. why not have that in Column A of
your client & Jobs sheets, this way your only have to use 1 reference
point instead of matching 2. You would only then need to use a LookUp()

For example:

Sheet("Clients")

ClientID = Column A
CLientLastName = Column B
ClientFirstName = Column C
ClientContactNo = Column D
ClientEmail = Column E


Sheet("Jobs")

Column B =
=IF($A2="","",LOOKUP($A2,Clients!$A$2:$E$1000,Clients!$B$2:$B$1000))
Column C =
=IF($A2="","",LOOKUP($A2,Clients!$A$2:$E$1000,Clients!$C$2:$C$1000))
Column D =
=IF($A2="","",LOOKUP($A2,Clients!$A$2:$E$1000,Clients!$D$2:$D$1000))
Column B =
=IF($A2="","",LOOKUP($A2,Clients!$A$2:$E$1000,Clients!$E$2:$E$1000))

Copy down as desired

HTH
Mick.

 

[Spencer101]

;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 sam
as
the ones above the difference being that in the telephone and emai
columns
I have a formula which populates these cells from the client workshee
using
the first and last name cells. The formula I use (for the telephon
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 th

firstname and lastname cells. I amended the formula to the following t

"hide" the error, which it does, but also leaves the cells blank afte
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,Clients!$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)),"")

 

[Andy Roberts]

I did think of this but they dont have an ID. They are simply names added
to a list as and when they make an enquiry to the business.

 

[Andy Roberts]

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

;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,Clients!$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)),"")
*