ExcelBanter - Index / Match Formula Problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Index / Match Formula Problem (https://www.excelbanter.com/excel-worksheet-functions/446360-index-match-formula-problem.html)

Index / Match Formula Problem

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

Index / Match Formula Problem

On 18/06/2012 10:10 PM, Andy Roberts wrote:
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

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,Clie nts!$B$2:$B$1000))
Column C =
=IF($A2="","",LOOKUP($A2,Clients!$A$2:$E$1000,Clie nts!$C$2:$C$1000))
Column D =
=IF($A2="","",LOOKUP($A2,Clients!$A$2:$E$1000,Clie nts!$D$2:$D$1000))
Column B =
=IF($A2="","",LOOKUP($A2,Clients!$A$2:$E$1000,Clie nts!$E$2:$E$1000))

Copy down as desired

HTH
Mick.

Quote:

Originally Posted by Andy Roberts[_3_] (Post 1602842)

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)),"")

Index / Match Formula Problem

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.

"Vacuum Sealed" wrote in message
ond.com...
On 18/06/2012 10:10 PM, Andy Roberts wrote:
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

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,Clie nts!$B$2:$B$1000))
Column C =
=IF($A2="","",LOOKUP($A2,Clients!$A$2:$E$1000,Clie nts!$C$2:$C$1000))
Column D =
=IF($A2="","",LOOKUP($A2,Clients!$A$2:$E$1000,Clie nts!$D$2:$D$1000))
Column B =
=IF($A2="","",LOOKUP($A2,Clients!$A$2:$E$1000,Clie nts!$E$2:$E$1000))

Copy down as desired

HTH
Mick.

Index / Match Formula Problem

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