Hi Experts,

Is there a formula/macro that I can apply to the results of a Vlookup
function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
Available Error" (#N/A) error with a 0?

I am running Excel Pro on Windows XP Pro SP 2.

I very much look forward to hearing from you.

kind regards,

Paul

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message ...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul

Hi Niek,

Thank you very much, that has fixed it.

Cheers,

Paul

"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message ...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul

I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message ...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul

See my reply to your identical question below. Replace "" by "PENDING"

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message ...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul

Fabulous! Thanks so much!

"KL" wrote:

See my reply to your identical question below. Replace "" by "PENDING"

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message ...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul

Hi, KL -

maybe you can help me with another problem related to this same function. I
used the formula you suggested below and it worked perfectly. Now I want to
know if it is possible to lookup 2 cell references, whereby both have to
match in order to yield the result. Here is my formula:

=IF(ISERROR(VLOOKUP(B8,'[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE)),"PENDING",VLOOKUP(B8, '[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE))

Is is possible to VLOOKUP both B8 (Last Name) and C8 (First Name) and only
if BOTH match does it yield my result, otherwise it indicates "Pending"?

Thanks so much for your help!


"KL" wrote:

See my reply to your identical question below. Replace "" by "PENDING"

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message ...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul

Hi MsBeverlee,

It sounds as if you want to lookup both First and Last Name in the same column. Is this true? If not please provide the range in
which you want to lookup C8.

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

maybe you can help me with another problem related to this same function. I
used the formula you suggested below and it worked perfectly. Now I want to
know if it is possible to lookup 2 cell references, whereby both have to
match in order to yield the result. Here is my formula:

=IF(ISERROR(VLOOKUP(B8,'[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE)),"PENDING",VLOOKUP(B8, '[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE))

Is is possible to VLOOKUP both B8 (Last Name) and C8 (First Name) and only
if BOTH match does it yield my result, otherwise it indicates "Pending"?

Thanks so much for your help!


"KL" wrote:

See my reply to your identical question below. Replace "" by "PENDING"

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message
...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul

Hi, KL -

Actually, the First Name and Last Name are in separate columns. Because
there are duplicate last names in my data, I need the VLOOKUP to look up both
of these and then return the rersult from column 3 only if both of these
criteria match, that way the correct result from column 3 is matched to the
correct last name.

For instance, in my example, $A2="Smith" and $B2="Cathy" and I want to
return the result of data in column 3 from my TRAVEL MANIFEST spreadsheet
(which is Flight Arrival Time), but only if both criteria ($A2 and $B2) are
met.

Thanks!

"KL" wrote:

Hi MsBeverlee,

It sounds as if you want to lookup both First and Last Name in the same column. Is this true? If not please provide the range in
which you want to lookup C8.

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

maybe you can help me with another problem related to this same function. I
used the formula you suggested below and it worked perfectly. Now I want to
know if it is possible to lookup 2 cell references, whereby both have to
match in order to yield the result. Here is my formula:

=IF(ISERROR(VLOOKUP(B8,'[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE)),"PENDING",VLOOKUP(B8, '[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE))

Is is possible to VLOOKUP both B8 (Last Name) and C8 (First Name) and only
if BOTH match does it yield my result, otherwise it indicates "Pending"?

Thanks so much for your help!


"KL" wrote:

See my reply to your identical question below. Replace "" by "PENDING"

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message
...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

MsBeverlee wrote:

Hi, KL -

Actually, the First Name and Last Name are in separate columns. Because
there are duplicate last names in my data, I need the VLOOKUP to look up both
of these and then return the rersult from column 3 only if both of these
criteria match, that way the correct result from column 3 is matched to the
correct last name.

For instance, in my example, $A2="Smith" and $B2="Cathy" and I want to
return the result of data in column 3 from my TRAVEL MANIFEST spreadsheet
(which is Flight Arrival Time), but only if both criteria ($A2 and $B2) are
met.

Thanks!

"KL" wrote:

Hi MsBeverlee,

It sounds as if you want to lookup both First and Last Name in the same column. Is this true? If not please provide the range in
which you want to lookup C8.

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

maybe you can help me with another problem related to this same function. I
used the formula you suggested below and it worked perfectly. Now I want to
know if it is possible to lookup 2 cell references, whereby both have to
match in order to yield the result. Here is my formula:

=IF(ISERROR(VLOOKUP(B8,'[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE)),"PENDING",VLOOKUP(B8, '[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE))

Is is possible to VLOOKUP both B8 (Last Name) and C8 (First Name) and only
if BOTH match does it yield my result, otherwise it indicates "Pending"?

Thanks so much for your help!


"KL" wrote:

See my reply to your identical question below. Replace "" by "PENDING"

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message
...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul







--

Dave Peterson

And what about the IF component of my original formula? Can that be
incorporated into this suggested formula you provided?

Thanks!


"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

MsBeverlee wrote:

Hi, KL -

Actually, the First Name and Last Name are in separate columns. Because
there are duplicate last names in my data, I need the VLOOKUP to look up both
of these and then return the rersult from column 3 only if both of these
criteria match, that way the correct result from column 3 is matched to the
correct last name.

For instance, in my example, $A2="Smith" and $B2="Cathy" and I want to
return the result of data in column 3 from my TRAVEL MANIFEST spreadsheet
(which is Flight Arrival Time), but only if both criteria ($A2 and $B2) are
met.

Thanks!

"KL" wrote:

Hi MsBeverlee,

It sounds as if you want to lookup both First and Last Name in the same column. Is this true? If not please provide the range in
which you want to lookup C8.

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

maybe you can help me with another problem related to this same function. I
used the formula you suggested below and it worked perfectly. Now I want to
know if it is possible to lookup 2 cell references, whereby both have to
match in order to yield the result. Here is my formula:

=IF(ISERROR(VLOOKUP(B8,'[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE)),"PENDING",VLOOKUP(B8, '[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE))

Is is possible to VLOOKUP both B8 (Last Name) and C8 (First Name) and only
if BOTH match does it yield my result, otherwise it indicates "Pending"?

Thanks so much for your help!


"KL" wrote:

See my reply to your identical question below. Replace "" by "PENDING"

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message
...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul







--

Dave Peterson

Also . . . what does the "0" at the end of your formula represent?

Thanks!


"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

MsBeverlee wrote:

Hi, KL -

Actually, the First Name and Last Name are in separate columns. Because
there are duplicate last names in my data, I need the VLOOKUP to look up both
of these and then return the rersult from column 3 only if both of these
criteria match, that way the correct result from column 3 is matched to the
correct last name.

For instance, in my example, $A2="Smith" and $B2="Cathy" and I want to
return the result of data in column 3 from my TRAVEL MANIFEST spreadsheet
(which is Flight Arrival Time), but only if both criteria ($A2 and $B2) are
met.

Thanks!

"KL" wrote:

Hi MsBeverlee,

It sounds as if you want to lookup both First and Last Name in the same column. Is this true? If not please provide the range in
which you want to lookup C8.

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

maybe you can help me with another problem related to this same function. I
used the formula you suggested below and it worked perfectly. Now I want to
know if it is possible to lookup 2 cell references, whereby both have to
match in order to yield the result. Here is my formula:

=IF(ISERROR(VLOOKUP(B8,'[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE)),"PENDING",VLOOKUP(B8, '[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE))

Is is possible to VLOOKUP both B8 (Last Name) and C8 (First Name) and only
if BOTH match does it yield my result, otherwise it indicates "Pending"?

Thanks so much for your help!


"KL" wrote:

See my reply to your identical question below. Replace "" by "PENDING"

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message
...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul







--

Dave Peterson

The 0 means that the match is looking for an exact match.

a2=othersheet!a1:a100
will return a bunch of true's and falses.

b2=othersheet!b1:b100
will return a bunch of true's and falses.

When you multiply true*true, you get 1. Any other combination will give 0.

So
match(1,{0,0,1,0,0,...,1,1,0},0)
will return the first row number where both values are what you want.

And =index(othersheet!c1:c100,somenumber)
will return the value in column C where the first name matches and the last name
matches (at the same time/on the same row).

MsBeverlee wrote:

Also . . . what does the "0" at the end of your formula represent?

Thanks!

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

MsBeverlee wrote:

Hi, KL -

Actually, the First Name and Last Name are in separate columns. Because
there are duplicate last names in my data, I need the VLOOKUP to look up both
of these and then return the rersult from column 3 only if both of these
criteria match, that way the correct result from column 3 is matched to the
correct last name.

For instance, in my example, $A2="Smith" and $B2="Cathy" and I want to
return the result of data in column 3 from my TRAVEL MANIFEST spreadsheet
(which is Flight Arrival Time), but only if both criteria ($A2 and $B2) are
met.

Thanks!

"KL" wrote:

Hi MsBeverlee,

It sounds as if you want to lookup both First and Last Name in the same column. Is this true? If not please provide the range in
which you want to lookup C8.

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

maybe you can help me with another problem related to this same function. I
used the formula you suggested below and it worked perfectly. Now I want to
know if it is possible to lookup 2 cell references, whereby both have to
match in order to yield the result. Here is my formula:

=IF(ISERROR(VLOOKUP(B8,'[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE)),"PENDING",VLOOKUP(B8, '[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE))

Is is possible to VLOOKUP both B8 (Last Name) and C8 (First Name) and only
if BOTH match does it yield my result, otherwise it indicates "Pending"?

Thanks so much for your help!


"KL" wrote:

See my reply to your identical question below. Replace "" by "PENDING"

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message
...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul







--

Dave Peterson


--

Dave Peterson

Thanks, Dave -

It worked! I really appreciate your help. The problem was I was forgetting
the hit CTRL+SHIFT+ENTER for the array formula result. Once I did that it
worked!

Now . . . can I pick your brain a little more? I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

Thanks!




"Dave Peterson" wrote:

The 0 means that the match is looking for an exact match.

a2=othersheet!a1:a100
will return a bunch of true's and falses.

b2=othersheet!b1:b100
will return a bunch of true's and falses.

When you multiply true*true, you get 1. Any other combination will give 0.

So
match(1,{0,0,1,0,0,...,1,1,0},0)
will return the first row number where both values are what you want.

And =index(othersheet!c1:c100,somenumber)
will return the value in column C where the first name matches and the last name
matches (at the same time/on the same row).

MsBeverlee wrote:

Also . . . what does the "0" at the end of your formula represent?

Thanks!

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

MsBeverlee wrote:

Hi, KL -

Actually, the First Name and Last Name are in separate columns. Because
there are duplicate last names in my data, I need the VLOOKUP to look up both
of these and then return the rersult from column 3 only if both of these
criteria match, that way the correct result from column 3 is matched to the
correct last name.

For instance, in my example, $A2="Smith" and $B2="Cathy" and I want to
return the result of data in column 3 from my TRAVEL MANIFEST spreadsheet
(which is Flight Arrival Time), but only if both criteria ($A2 and $B2) are
met.

Thanks!

"KL" wrote:

Hi MsBeverlee,

It sounds as if you want to lookup both First and Last Name in the same column. Is this true? If not please provide the range in
which you want to lookup C8.

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

maybe you can help me with another problem related to this same function. I
used the formula you suggested below and it worked perfectly. Now I want to
know if it is possible to lookup 2 cell references, whereby both have to
match in order to yield the result. Here is my formula:

=IF(ISERROR(VLOOKUP(B8,'[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE)),"PENDING",VLOOKUP(B8, '[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE))

Is is possible to VLOOKUP both B8 (Last Name) and C8 (First Name) and only
if BOTH match does it yield my result, otherwise it indicates "Pending"?

Thanks so much for your help!


"KL" wrote:

See my reply to your identical question below. Replace "" by "PENDING"

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?



"Niek Otten" wrote:

Hi Paul,

=IF(ISNA(YourFormula),0,YourFormula)

--
Kind regards,

Niek Otten

"Lancer940" wrote in message
...
| Hi Experts,
|
| Is there a formula/macro that I can apply to the results of a Vlookup
| function(=VLOOKUP($A$6:$A$119,Swe_Lookup,2,FALSE)) to replace the "Value Not
| Available Error" (#N/A) error with a 0?
|
| I am running Excel Pro on Windows XP Pro SP 2.
|
| I very much look forward to hearing from you.
|
| kind regards,
|
| Paul







--

Dave Peterson


--

Dave Peterson

"MsBeverlee" wrote
... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)),"",index(othersheet!$c$ 1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36

Hi, KL -

I tried it but it didn't work, so I'm sure I'm doing something wrong. Here
is my formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

It is yielding the correct result. But now I want to add the IF componenet
so that if the result is #N/A (because some of the info is missing in the
other worksheet), I want it to return "PENDING" instead of the #N/A. How do
I do this?

Thanks so much!


"KL" wrote:

"MsBeverlee" wrote
... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)),"",index(othersheet!$c$ 1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36

One other thing. Previously I was using the following VLOOKUP formula with
an IF component:

=IF(ISERROR(VLOOKUP(B45,'[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE)),"PENDING",VLOOKUP(B45 ,'[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,3,FALSE))

This yielded the "PENDING" result instead of the #N/A result. However, the
overall formula wasn't exactly what I needed because it only looked up 1
column and I need it to look up and match 2 columns (hence why I changed to
the INDEX & MATCH formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

However, the IF component functioned correctly in my previous formula. Will
it work the same with the new INDEX & MATCH formula?

Thanks!

"KL" wrote:

"MsBeverlee" wrote
... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)),"",index(othersheet!$c$ 1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36

This worked for me (don't forget to use CTRL+SHIFT+ENTER when introducing the formula):

=IF(ISNA(MATCH(1,(B47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0)),"PENDING",INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

I tried it but it didn't work, so I'm sure I'm doing something wrong. Here
is my formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

It is yielding the correct result. But now I want to add the IF componenet
so that if the result is #N/A (because some of the info is missing in the
other worksheet), I want it to return "PENDING" instead of the #N/A. How do
I do this?

Thanks so much!


"KL" wrote:

"MsBeverlee" wrote
... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many
instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)),"",index(othersheet!$c$ 1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36

It still isn't working properly. For instance, it is giving me the result
"PENDING" even though the data is there. It should be returning the result
"NL2PWW" because it should be pulling the data from column 3 of my TRAVEL
MANIFEST spreadsheet. And if there is no data in column 3 of my TRAVEL
MANIFEST spreadsheet, then it should return the result "PENDING".

What am I doing wrong?


"KL" wrote:

This worked for me (don't forget to use CTRL+SHIFT+ENTER when introducing the formula):

=IF(ISNA(MATCH(1,(B47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0)),"PENDING",INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

I tried it but it didn't work, so I'm sure I'm doing something wrong. Here
is my formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

It is yielding the correct result. But now I want to add the IF componenet
so that if the result is #N/A (because some of the info is missing in the
other worksheet), I want it to return "PENDING" instead of the #N/A. How do
I do this?

Thanks so much!


"KL" wrote:

"MsBeverlee" wrote
... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many
instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)),"",index(othersheet!$c$ 1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36

First, I'd drop the ,3) at the end. And just use

index('[travel manifest_master.xls]sheet1'$c$2:$c$66,....

You know you want column C, so why make the formula just a little bit more
complex than it has to be.

Second, I don't like this kind of thing.

=if(iserror(somelongformula),"warning","somelongfo rmula")

Especially when the formula gets really complex. I know I'll screw it up when I
have to change it.

(In fact, xl2007 added an =iferror() function to stop this kind of redundant
formula.)

I'd use two cells in two columns.

One with the long formula and one that looks at the result.

(Say column X and column Y)
The long formula will be in column X.
=if(iserror(x2),"warning",x2)

And then I can even hide column X to make it look pretty.

And my brain can go back thinking about donuts---hmmm, donuts.


MsBeverlee wrote:

It still isn't working properly. For instance, it is giving me the result
"PENDING" even though the data is there. It should be returning the result
"NL2PWW" because it should be pulling the data from column 3 of my TRAVEL
MANIFEST spreadsheet. And if there is no data in column 3 of my TRAVEL
MANIFEST spreadsheet, then it should return the result "PENDING".

What am I doing wrong?

"KL" wrote:

This worked for me (don't forget to use CTRL+SHIFT+ENTER when introducing the formula):

=IF(ISNA(MATCH(1,(B47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0)),"PENDING",INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

I tried it but it didn't work, so I'm sure I'm doing something wrong. Here
is my formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

It is yielding the correct result. But now I want to add the IF componenet
so that if the result is #N/A (because some of the info is missing in the
other worksheet), I want it to return "PENDING" instead of the #N/A. How do
I do this?

Thanks so much!


"KL" wrote:

"MsBeverlee" wrote
... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many
instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)),"",index(othersheet!$c$ 1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36





--

Dave Peterson

"MsBeverlee" wrote in message ...
It still isn't working properly. For instance, it is giving me the result
"PENDING" even though the data is there. It should be returning the result
"NL2PWW" because it should be pulling the data from column 3 of my TRAVEL
MANIFEST spreadsheet. And if there is no data in column 3 of my TRAVEL
MANIFEST spreadsheet, then it should return the result "PENDING".
What am I doing wrong?

As already mentioned by Dave, it's easy to screw up with such a long formula and I mentioned before that it can affect the
performance of the file as a whole. I would go with Dave's solution or even leave the initial formula as is and have the error
values hidden by Conditional Formats (at the end of the day it is about the sheet looking pretty :-))

I repeted the tests and formula as posted by me works well. There are several things that can wrong with the formula such as:
1) the values you are searching are not exactly the same in your table although they might look the same (some extra spaces or
ather invisible caracters)
2) when sent to a newsgroup troughth OE the lines in the message are broken so when you copy the formula back to your sheet some
characters may be lost or added by mistake (e.g. spaces in the name of the remote workbook)
3) if you are not using an English version of Office, the names of functions and separators must be translated to your locale

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36




"KL" wrote:

This worked for me (don't forget to use CTRL+SHIFT+ENTER when introducing the formula):

=IF(ISNA(MATCH(1,(B47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0)),"PENDING",INDEX('[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

I tried it but it didn't work, so I'm sure I'm doing something wrong. Here
is my formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

It is yielding the correct result. But now I want to add the IF componenet
so that if the result is #N/A (because some of the info is missing in the
other worksheet), I want it to return "PENDING" instead of the #N/A. How do
I do this?

Thanks so much!


"KL" wrote:

"MsBeverlee" wrote
... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many
instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)),"",index(othersheet!$c$ 1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36

Thanks for all of your help. For whatever reason, it still is not working.
I'm not sure why, but I don't think it's the reasons you suggested. And I
only say that because I am copying and pasting Dave's suggested formula right
into my spreadsheet. But for whatever reason, it's only returning the
"PENDING" result regardless of whether it's a "Value Not Available" (#N/A)
result or not. By just using my original formula

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

this works perfectly and returns the correct results. I was only trying to
replace the #N/A with "PENDING" for those cells that data isn't available in
the TRAVEL MANIFEST spreadsheet. I thought I could use an IF component to
accomplish this, but apparently it's not possible. I don't know why the IF
component doesn't work. I guess I'll just have to deal with it.

I do appreciate all of your suggested help. Thanks so much!



"KL" wrote:

"MsBeverlee" wrote in message ...
It still isn't working properly. For instance, it is giving me the result
"PENDING" even though the data is there. It should be returning the result
"NL2PWW" because it should be pulling the data from column 3 of my TRAVEL
MANIFEST spreadsheet. And if there is no data in column 3 of my TRAVEL
MANIFEST spreadsheet, then it should return the result "PENDING".
What am I doing wrong?

As already mentioned by Dave, it's easy to screw up with such a long formula and I mentioned before that it can affect the
performance of the file as a whole. I would go with Dave's solution or even leave the initial formula as is and have the error
values hidden by Conditional Formats (at the end of the day it is about the sheet looking pretty :-))

I repeted the tests and formula as posted by me works well. There are several things that can wrong with the formula such as:
1) the values you are searching are not exactly the same in your table although they might look the same (some extra spaces or
ather invisible caracters)
2) when sent to a newsgroup troughth OE the lines in the message are broken so when you copy the formula back to your sheet some
characters may be lost or added by mistake (e.g. spaces in the name of the remote workbook)
3) if you are not using an English version of Office, the names of functions and separators must be translated to your locale

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36




"KL" wrote:

This worked for me (don't forget to use CTRL+SHIFT+ENTER when introducing the formula):

=IF(ISNA(MATCH(1,(B47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0)),"PENDING",INDEX('[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

I tried it but it didn't work, so I'm sure I'm doing something wrong. Here
is my formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

It is yielding the correct result. But now I want to add the IF componenet
so that if the result is #N/A (because some of the info is missing in the
other worksheet), I want it to return "PENDING" instead of the #N/A. How do
I do this?

Thanks so much!


"KL" wrote:

"MsBeverlee" wrote
... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many
instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)),"",index(othersheet!$c$ 1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36

My formula didn't use A2:Q66 and offset by 3 columns.

But are you sure you used ctrl-shift-enter to enter the formula.

MsBeverlee wrote:

Thanks for all of your help. For whatever reason, it still is not working.
I'm not sure why, but I don't think it's the reasons you suggested. And I
only say that because I am copying and pasting Dave's suggested formula right
into my spreadsheet. But for whatever reason, it's only returning the
"PENDING" result regardless of whether it's a "Value Not Available" (#N/A)
result or not. By just using my original formula

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

this works perfectly and returns the correct results. I was only trying to
replace the #N/A with "PENDING" for those cells that data isn't available in
the TRAVEL MANIFEST spreadsheet. I thought I could use an IF component to
accomplish this, but apparently it's not possible. I don't know why the IF
component doesn't work. I guess I'll just have to deal with it.

I do appreciate all of your suggested help. Thanks so much!



"KL" wrote:

"MsBeverlee" wrote in message ...
It still isn't working properly. For instance, it is giving me the result
"PENDING" even though the data is there. It should be returning the result
"NL2PWW" because it should be pulling the data from column 3 of my TRAVEL
MANIFEST spreadsheet. And if there is no data in column 3 of my TRAVEL
MANIFEST spreadsheet, then it should return the result "PENDING".
What am I doing wrong?

As already mentioned by Dave, it's easy to screw up with such a long formula and I mentioned before that it can affect the
performance of the file as a whole. I would go with Dave's solution or even leave the initial formula as is and have the error
values hidden by Conditional Formats (at the end of the day it is about the sheet looking pretty :-))

I repeted the tests and formula as posted by me works well. There are several things that can wrong with the formula such as:
1) the values you are searching are not exactly the same in your table although they might look the same (some extra spaces or
ather invisible caracters)
2) when sent to a newsgroup troughth OE the lines in the message are broken so when you copy the formula back to your sheet some
characters may be lost or added by mistake (e.g. spaces in the name of the remote workbook)
3) if you are not using an English version of Office, the names of functions and separators must be translated to your locale

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36




"KL" wrote:

This worked for me (don't forget to use CTRL+SHIFT+ENTER when introducing the formula):

=IF(ISNA(MATCH(1,(B47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0)),"PENDING",INDEX('[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36


"MsBeverlee" wrote in message ...
Hi, KL -

I tried it but it didn't work, so I'm sure I'm doing something wrong. Here
is my formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

It is yielding the correct result. But now I want to add the IF componenet
so that if the result is #N/A (because some of the info is missing in the
other worksheet), I want it to return "PENDING" instead of the #N/A. How do
I do this?

Thanks so much!


"KL" wrote:

"MsBeverlee" wrote
... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many
instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)),"",index(othersheet!$c$ 1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=o thersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36







--

Dave Peterson

This code works well for me:

'-----Code Start-----
Option Explicit

'IMPORTANT!!!
'Make sure your original file "TRAVEL MANIFEST_Master.xls"
'is not in the same folder as the file from which you run this code.
Sub Test()
Dim oWB As Workbook
Dim strWBName As String
Dim arrRng As Variant
Dim strFormulaPart1 As String
Dim strFormulaPart2 As String

strWBName = ThisWorkbook.Path & "\" & "TRAVEL MANIFEST_Master.xls"

If Dir(strWBName) < "" Then
On Error Resume Next
Set oWB = Workbooks(Dir(strWBName))
If Err.Number < 0 Then Set oWB = Workbooks.Open(strWBName)
On Error GoTo 0
Else
Set oWB = Workbooks.Add
oWB.SaveAs strWBName
End If
With oWB.Worksheets(1)
.[A47:C47] = Array("John", "Smith", 123456)
arrRng = Array(.[A2:A66].Address(True, True, xlA1, True), _
.[B2:B66].Address(True, True, xlA1, True), _
.[C2:C66].Address(True, True, xlA1, True))
End With
strFormulaPart1 = "=IF(ISNA(MATCH(1,(B47=" & arrRng(0) & ")*(C47=" & arrRng(1) & "),0)),""PENDING"",DUMMY())"
strFormulaPart2 = "INDEX(" & arrRng(2) & ",MATCH(1,(B47=" & arrRng(0) & ")*(C47=" & arrRng(1) & "),0))"

With Worksheets(1)
.Range("B47:C47") = Array("John", "Smith")
.Range("B48:C48") = Array("James", "Smith")
With .Range("D47")
.FormulaArray = strFormulaPart1
.Replace "DUMMY()", strFormulaPart2
.AutoFill Destination:=.Resize(2)
End With
End With

oWB.Close True
Set oWB = Nothing
End Sub
'-----Code Finish-----

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36