VLOOKUP - returning the searched for value if it does not exist

[njuneardave]

I am searching for a value from one table to another (the value is located in
Column A in Sheet2 and column H in Sheet1). The formula seen below is what I
have been using (in Sheet3)

=IF(ISNA(VLOOKUP(Sheet2!A2,Sheet1!$H$2:$H$9000,1,FALSE)),IF(BTypesInputTable!A2 = "","",BTypesInputTable!A2),"")


the IF statement will convert a null value to a blank space instead of a
zero. But, if the value that im searching for is deleted in Sheet2, i get an
#N/A. Shouldnt this formula just skip a deleted row and go to the next?
(the row is completely deleted....it is not a blank row)


here is what the formula looks like after i try to search for that missing
value:


=IF(ISNA(VLOOKUP(Sheet2!#REF!,Sheet1!$H$2:$H$9000,1,FALSE)),IF(Sheet2!#REF!
= "", "", Sheet2!#REF!),"")

excel places that #REF! in my formula if the value USED to exist and then
does not anymore.


any ideas on how to fix this?


thanks in advance!

 

[Franz Verga]

Nel post *njuneardave* ha scritto:

I am searching for a value from one table to another (the value is
located in
Column A in Sheet2 and column H in Sheet1). The formula seen below
is what I
have been using (in Sheet3)

=IF(ISNA(VLOOKUP(Sheet2!A2,Sheet1!$H$2:$H$9000,1,FALSE)),IF(BTypesInputTable!A2
= "","",BTypesInputTable!A2),"")


the IF statement will convert a null value to a blank space instead
of a
zero. But, if the value that im searching for is deleted in Sheet2,
i get an #N/A. Shouldnt this formula just skip a deleted row and go
to the next? (the row is completely deleted....it is not a blank row)

Such a beaviour is due because the value you are looking for is not in the
same sheet af the formula. To skip the problem you can use the INDIRECT
function:

=IF(ISNA(VLOOKUP(INDIRECT("Sheet2!A2"),Sheet1!$H$2:$H$9000,1,FALSE)),IF(BTypesInputTable!A2=
"","",BTypesInputTable!A2),"")



--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[njuneardave]

Wow, thanks Franz, that worked great. Now a follow-up question: I am having
the same problem using INDEX and MATCH....here is what I have:


=IF(INDEX(Sheet2!$B$2:$B$9000,MATCH(1,(Sheet1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0))
<>
INDEX(Sheet1!$B$2:$B$9000,MATCH(1,(Sheet2!$A$2:$A$9000=Sheet1!$A2)*(Sheet2!$E$2:$E$9000=Sheet1!$E2),0)),
INDEX(Sheet2!$B$2:$B$9000,MATCH(1,(Sheet1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0)), "")


I am trying to see if the value in sheet1 and sheet2 that are respective to
their identifiers (in columns A and E) are equal. If they are equal, i dont
show anything, but if they are different, I want to show the value in Sheet2.
Is this formula way over complicated? Also, can i use indirect on this
formula to get rid of the #N/A references?


Thanks in advance!

 

[Franz Verga]

Nel post *njuneardave* ha scritto:

Wow, thanks Franz, that worked great. Now a follow-up question: I
am having
the same problem using INDEX and MATCH....here is what I have:


=IF(INDEX(Sheet2!$B$2:$B$9000,MATCH(1,(Sheet1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0))
<>
INDEX(Sheet1!$B$2:$B$9000,MATCH(1,(Sheet2!$A$2:$A$9000=Sheet1!$A2)*(Sheet2!$E$2:$E$9000=Sheet1!$E2),0)),
INDEX(Sheet2!$B$2:$B$9000,MATCH(1,(Sheet1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0)),
"")


I am trying to see if the value in sheet1 and sheet2 that are
respective to
their identifiers (in columns A and E) are equal. If they are equal,
i dont
show anything, but if they are different, I want to show the value in
Sheet2.
Is this formula way over complicated? Also, can i use indirect on
this
formula to get rid of the #N/A references?

Yes, in the same manner I showed you in my last post...

Thanks in advance!

You're welcome (in advance...)


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[njuneardave]

Franz, I tried what you said.....this is what I put:


=IF(ISERROR(INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0))),
"",
IF(INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0))
=
INDEX(Sheet1!B$2:B$9000,MATCH(1,(Sheet2!$A$2:$A$9000=Sheet1!$A2)*(Sheet2!$E$2:$E$9000=Sheet1!$E2),0)),
"",
INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0))))


for some, it returns an #N/A. for others it returns a number....but the
index is off... i want to either return the value at A2 for Sheet2 or nothing
at all. What is wrong with this?


Thanks alot, i really appreciate the help

 

[Franz Verga]

Nel post *njuneardave* ha scritto:

Franz, I tried what you said.....this is what I put:


=IF(ISERROR(INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0))),
"",
IF(INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0))
=
INDEX(Sheet1!B$2:B$9000,MATCH(1,(Sheet2!$A$2:$A$9000=Sheet1!$A2)*(Sheet2!$E$2:$E$9000=Sheet1!$E2),0)),
"",
INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0))))


for some, it returns an #N/A. for others it returns a number....but
the index is off... i want to either return the value at A2 for
Sheet2 or nothing at all. What is wrong with this?

Here it seems you don't use the INDIRECT function... So obviously it returns
#N/A. You have to "incapsulate" your references inside the INDIRECT
function, when you think you could delete some row; this is an example:

INDIRECT("your reference")

so the first INDEX MATCH of your formula becomes:

INDEX(INDIRECT("Sheet2!B$2:B$9000"),MATCH(1,(Sheet1!$A$2:$A$9000=INDIRECT("Sheet2!$A2"))*(Sheet1!$E$2:$E$9000=INDIRECT("Sheet2!$E2")),0))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[njuneardave]

sorry about the double post....i thought it should go under a new topic
because its a new question.

I tried what you suggested:

=IF(ISERROR(VLOOKUP(INDIRECT("Sheet2!A2"),Sheet1!$A$2:$A$9000,1,FALSE)),IF(INDIRECT("Sheet2!E2")
="","",INDIRECT("Sheet2!E2")),IF(ISERROR(INDEX(INDIRECT("Sheet2!$E$2:$E$9000"),MATCH(1,(Sheet1!$A$2:$A$9000=INDIRECT("Sheet2!A2"))*(Sheet1!$E$2:$E$9000=INDIRECT("Sheet2!E2")),0))),"",INDEX(INDIRECT("Sheet2!$E$2:$E$9000"),MATCH(1,(Sheet1!$A$2:$A$9000=INDIRECT("Sheet2!A2"))*(Sheet1!$E$2:$E$9000=INDIRECT("Sheet2!E2")),0))))


using INDIRECT everytime Sheet2 is called gives me concrete answers
everytime I copy it....obviously nothing changes. I do not understand the
INDIRECT function.

When I alter the INDIRECT formula and only leave it in a few places, it
isn't returning #N/A but i am getting incorrect answers.... it seems the
indices of what i want to return are skewed. Instead of getting, for
example, type3.... i am getting back an unexpected type5.....or a type12
instead of type5

 

[njuneardave]

Franz,

I finally got it to work. Thank you for your help. You are excellent!