INDIRECT within a LOOKUP

excel.jpg

My original formula looks like this.
=LOOKUP(2,1/COUNTIF(SHEET1!A1,{"TEST1","TEST2"}),{"T1","T2"})

And this works fine but I would like to use the INDIRECT function inside this LOOKUP function. So it would work like this formula.
=LOOKUP(2,1/COUNTIF(SHEET1!A1,{"INDIRECT("D1")","INDIRECT("D1")"}),{"T1","T2"})

I know this second formula is not correct but I am not sure how to get this to work.
Thanks for any help.

TESTBOOK1.xlsx

Originally Posted by strik009

... but I would like to use the INDIRECT function inside this LOOKUP function...

Why do you want to use INDIRECT inside the function?

Array constants (i.e. enclosed with the curly brackets) can not contain cell references or formulae.

Pete

Yes that is exactly rite XOR LX this is a simpler version of a more complex formula. I had another post on here that now one could seem to understand what it was I was trying to do.
Now that I have simplified this seems ( with your help ) I have finally gotten somewhere.
My actual formula looks like this.

=IF(SHEET1!A2="","",IFERROR(LOOKUP(2,1/COUNTIF(OFFSET('DATA'!$A$1,(ROWS($1:1)-1)*3,0),{"test1","test2","test3","test4"}),{"t1","t2","t3","t4"}),"UNK"))

I am looking for a way to manually change test1, test 2........ and t1, t2.......
I have another page that is called RULES I want the user to be able to change the info inside those cells and it reflect in this formula.
So D1:D4 (test1 : test4) and E1:E4 ( t1 :t4 ) are on the rules sheet ( RULES ) and the other sheets are ( SHEET1 ) and ( DATA ).

Do you think you can help me further by showing me how to to the same to the second part of the formula ... this part
{"t1","t2","t3","t4"})

Thanks again for your help.

Man is it really that simple?
I admit I am new at Excel and formulas perhaps I overcomplicated I will try your suggestion.
I hope it works.

ok so the formula is not quite working.
I believe because RULES!D1:D4 is to match with RULES!E1:E4
But the formula (I think) is not associating D1 with E1, D2 with E2, D3 with E3, D4 with E4 specifically.
I will attach my workbook so you can see.

TESTBOOK1.xlsx

Oh ok so its all in the work book but here this is the idea.
=IF(SHEET1!B1="","",IFERROR(LOOKUP(2,1/COUNTIF(OFFSET(DATA!$A$1,(ROWS($1:1)-1)*3,0),RULES!$D$1:$D$4),RULES!$E$1:$E$4),"UNK"))
Is the formula I put in SHEET1 A1.

and it seems to work....
but then I put this formula in SHEET1 A2.
=IF(SHEET1!B2="","",IFERROR(LOOKUP(2,1/COUNTIF(OFFSET(DATA!$A$4,(ROWS($1:1)-1)*3,0),RULES!$D$1:$D$4),RULES!$E$1:$E$4),"UNK"))
and it comes up and UNK.

I get the same problem for the next two formulas inside A3 and A4, they come up as UNK
=IF(SHEET1!B3="","",IFERROR(LOOKUP(2,1/COUNTIF(OFFSET(DATA!$A$7,(ROWS($1:1)-1)*3,0),RULES!$D$1:$D$4),RULES!$E$1:$E$4),"UNK"))
=IF(SHEET1!B4="","",IFERROR(LOOKUP(2,1/COUNTIF(OFFSET(DATA!$A$10,(ROWS($1:1)-1)*3,0),RULES!$D$1:$D$4),RULES!$E$1:$E$4),"UNK"))

I think its because its not specifically matching RULES D2, to E2 and D3 to E3 and D4 to E4.
I think perhaps because the ( : ) kinda messes that up? I think?

Yes. The first field works but after that I get UNK.
I am thinking perhaps something like
=IF(SHEET1!B1="","",IFERROR(LOOKUP(2,1/COUNTIF(OFFSET(DATA!$A$1,(ROWS($1:1)-1)*3,0),RULES!D1,D2,D3,D4),RULES!E1,E2,E3,E4),"UNK"))
?
I'll try that

No that did not work at all.

Dont give up on me now?