Vlookup "Value Not Available" (#N/A) Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

| 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"?
 
Fabulous! Thanks so much!

KL said:
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


MsBeverlee said:
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?
Click to expand...
Click to expand...
 
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 said:
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


MsBeverlee said:
I am having the same problem. Can you also use this same formula but instead
of returning "0" have it yield "PENDING"?
Click to expand...
Click to expand...
 
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


MsBeverlee said:
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 said:
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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



:

Hi Paul,

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

--
Kind regards,

Niek Otten

| 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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


MsBeverlee said:
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 said:
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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



:

Hi Paul,

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

--
Kind regards,

Niek Otten

| 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
Click to expand...
Click to expand...
Click to expand...
 
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))
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 said:
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


MsBeverlee said:
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!


:

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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



:

Hi Paul,

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

--
Kind regards,

Niek Otten

| 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
Click to expand...
Click to expand...
Click to expand...
 
And what about the IF component of my original formula? Can that be
incorporated into this suggested formula you provided?

Thanks!


Dave Peterson said:
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))
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 said:
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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!


:

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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



:

Hi Paul,

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

--
Kind regards,

Niek Otten

| 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
Click to expand...
Click to expand...
Click to expand...
 
Also . . . what does the "0" at the end of your formula represent?

Thanks!


Dave Peterson said:
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))
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 said:
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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!


:

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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



:

Hi Paul,

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

--
Kind regards,

Niek Otten

| 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
Click to expand...
Click to expand...
Click to expand...
 
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).
Also . . . what does the "0" at the end of your formula represent?

Thanks!

Dave Peterson said:
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))
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!

:

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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!


:

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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



:

Hi Paul,

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

--
Kind regards,

Niek Otten

| 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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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).
Also . . . what does the "0" at the end of your formula represent?

Thanks!

Dave Peterson said:
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!

:

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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!


:

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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



:

Hi Paul,

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

--
Kind regards,

Niek Otten

| 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
Click to expand...
Click to expand...
Click to expand...
 
... 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?
Click to expand...

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=othersheet!$b$1:$b$100),0)),"",index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$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/profile=AB32F8EE-8ED3-4AB9-ADDA-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 said:
... 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?
Click to expand...

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=othersheet!$b$1:$b$100),0)),"",index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
Click to expand...
 
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 said:
... 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?
Click to expand...

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=othersheet!$b$1:$b$100),0)),"",index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
Click to expand...
 
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


MsBeverlee said:
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 said:
... 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?
Click to expand...

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=othersheet!$b$1:$b$100),0)),"",index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
Click to expand...
Click to expand...
 
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 said:
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


MsBeverlee said:
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 said:
... 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=othersheet!$b$1:$b$100),0)),"",index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
Click to expand...
Click to expand...
Click to expand...
 
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","somelongformula")

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.

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 said:
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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


MsBeverlee said:
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!


:

... 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=othersheet!$b$1:$b$100),0)),"",index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
Click to expand...
Click to expand...
Click to expand...
 
Back
Top