Excel 寫Vlookup的問題

[匿名]

2010-03-09 7:06 pm

A8 的值是驗證的有四種選項分別為week1,week2,week3,week4
A9 的值是驗證的有四種選項分別為1,2,3,4

某欄位本來是
=IF(VLOOKUP(D7,'’week1!$C$7:$M$111,6,FALSE)=0,"",VLOOKUP(D7,’week1'!$C$7:$M$111,6,FALSE))

Vlookup(值,某個sheet的陣列, 抓取值, false)
我想讓Sheet 跟抓取值可以靠我從A8. A9的驗證欄位去控制, 大概像下面這樣子
=IF(VLOOKUP(D7,'A8'!$C$7:$M$111,A9+5,FALSE)=0,"",VLOOKUP(D7,'A8'!$C$7:$M$111,A9+5,FALSE))

請問要怎麼寫呢???? 有辦法嗎?

回答 (2)

piny

2010-03-09 8:28 pm

✔ 最佳答案

請試試

=IF(VLOOKUP(D7,INDIRECT(A8&"!$C$7:$M$111"),A9+5,),"",VLOOKUP(D7,INDIRECT(A8&"!$C$7:$M$111"),A9+5,))

👍 0 👎 0

Andy Chan

2010-03-10 4:16 pm

=IF(ISNA(VLOOKUP(D7,INDIRECT(A8&"!$C$7:$M$111"),A9+5,)),"",VLOOKUP(D7,INDIRECT(A8&"!$C$7:$M$111"),A9+5,))

👍 0 👎 0