INDEX - MATCH - VLOOKUP - returning missing reference

[Guest]

I am searching for a value from one table to another. The value has two
identifiers that form a unique combination (in columns A and E). If the
value in Sheet2 has no corresponding value in Sheet1 (meaning that the
columns A and E in Sheet2 had no match in Sheet1), the value is to be
returned to the cell. If the value IS in Sheet2, I want to compare the
values, and if the two values are different, I want to return the value in
Sheet2 to the cell. The formula seen below is what I have been using (in
Sheet3) for row # 6.

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


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)


thanks in advance for the help....it will be much appreciated!

 

[Franz Verga]

Nel post *njuneardave* ha scritto:

I am searching for a value from one table to another. The value has
two identifiers that form a unique combination (in columns A and E).
If the value in Sheet2 has no corresponding value in Sheet1 (meaning
that the columns A and E in Sheet2 had no match in Sheet1), the value
is to be returned to the cell. If the value IS in Sheet2, I want to
compare the values, and if the two values are different, I want to
return the value in Sheet2 to the cell. The formula seen below is
what I have been using (in Sheet3) for row # 6.

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


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)


thanks in advance for the help....it will be much appreciated!

Don't multipost.

 

[Guest]

sorry franz, I thought that it was a new question and needed a new thread. I
won't let it happen again.