Indirect Lookup Other Worksheet

ScreenHunter_008.jpg

The formula at C2 to C4 are something like this.

When A2=1, C2=Lookup(B2, Sheet1!(A2:A5), Sheet1!(B2:B5))
When A2=2, C2=Lookup(B2, Sheet1!(A6:A9), Sheet1!(B6:B9))
When A2=3, C2=Lookup(B2, Sheet1!(A10:A13), Sheet1!(B10:B13))

What are the formula in C2 to C4, in terms of INDIRECT, OFFSET, ADDRESS , or by other means are alright.

Try this ...

=LOOKUP(B2,CHOOSE(A2,Sheet1!$A$2:$B$5,Sheet1!$A$6:$B$9,Sheet1!$A$10:$B$13))

Originally Posted by Phuocam

Try this ...

=LOOKUP(B2,CHOOSE(A2,Sheet1!$A$2:$B$5,Sheet1!$A$6:$B$9,Sheet1!$A$10:$B$13))

I want to make use of the cells A2 to A4. Something like...

C2=Lookup(B2, indirect(Sheet1!("A$"&(A2*4-2):"A$"&(A2*4+1)), indirect(Sheet1!("B$"&(A2*4-2):"B$"&(A2*4+1))

It is ...

=LOOKUP(B2, INDIRECT("Sheet1!A$"&(A2*4-2)&":B$"&A2*4+1))

Originally Posted by Phuocam

It is ...

=LOOKUP(B2, INDIRECT("Sheet1!A$"&(A2*4-2)&":B$"&A2*4+1))

That's exactly what I want, thank you Phuocam.

You are welcome!