VLOOKUP - returning the searched for value if it does not exis
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,(Sheet 1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sh eet2!$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$90 00=Sheet1!$A2)*(Sheet2!$E$2:$E$9000=Sheet1!$E2),0) ),
"",
INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A$90 00=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,(Sheet 1!$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
|