INDEX - MATCH - VLOOKUP - returning missing reference

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!

Nel post news:[email protected]
*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.

--
Ciao

Franz Verga from Italy

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

"njuneardave" wrote:

> 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!
>