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(IND IRECT("Sheet2!$E$2:$E$9000"),MATCH(1,(Sheet1!$A$2: $A$9000=INDIRECT("Sheet2!A2"))*(Sheet1!$E$2:$E$900 0=INDIRECT("Sheet2!E2")),0))),"",INDEX(INDIRECT("S heet2!$E$2:$E$9000"),MATCH(1,(Sheet1!$A$2:$A$9000= INDIRECT("Sheet2!A2"))*(Sheet1!$E$2:$E$9000=INDIRE CT("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

"Franz Verga" wrote:

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