How to Lookup Value

[m9vukyem]

Dear sir,
I need a formula for find out the value of Each employee from Drop down list

 

[GraH - Guido]

Maybe,
=VLOOKUP($B$5,'80C'!$B$1:$C$6,MATCH('Emp-1'!$A12,'80C'!$B$1:$H$1,0),0)

You will need to adjust the ranges to your final version.

 

[m9vukyem]

That 80c sheet is Dummy, actually I find out the figure from calculator Sheet

 

[GraH - Guido]

Then try (but using 2 volatile functions)
=OFFSET(INDIRECT(ADDRESS(MATCH($B$5,Caclulator!B:B,0),7,1,1,"Caclulator")),MATCH(A13,Caclulator!$F$2:$F$9,0)-1,,1,1)

 

[m9vukyem]

Thank you sir

 

[bosco_yip]

Then try (but using 2 volatile functions)
=OFFSET(INDIRECT(ADDRESS(MATCH($B$5,Caclulator!B:B,0),7,1,1,"Caclulator")),MATCH(A13,Caclulator!$F$2:$F$9,0)-1,,1,1)

Hi GraH - Guido,

In "Emp-1" sheet B5, if you select "Employee 2" and enter value in "Caclulator" sheet G15.

You will find that B17 formula will return wrong result.



Regards
Bosco

 

[GraH - Guido]

Thx @bosco_yip .

2 things:
1. It's a typo: at my end it works with =OFFSET(INDIRECT(ADDRESS(MATCH($B$5;Caclulator!B:B;0);7;1;1;"Caclulator"));MATCH(A12;Caclulator!$F$2:$F$9;0)-1;;1;1)
2. The range is different per block of Employee, I missed that. So the formula is of when past "row 7".



Do you have some magic up your sleeve to make something more elegant?

Without that row 7

 

[bosco_yip]

Thx @bosco_yip ................
Do you have some magic up your sleeve to make something more elegant?

So, this is my Offset simple formula solution,

1] Delete "Caclulator" sheet Row 7 of which appear blank in cell F7

2] In "Emp-1" sheet B12, formula copied down :

=OFFSET(Caclulator!$G$1,MATCH($B$5,Caclulator!B:B,0)+MATCH(A12,Caclulator!$F$2:$F$8,0)-2,,)




Regards

 

[GraH - Guido]

Then won't this be okay too?
=INDEX(Caclulator!$G:$G,MATCH($B$5,Caclulator!B:B,0)+MATCH(A12,Caclulator!$F$1:$F$8,0)-2)