fhzhkunming
New Member
- Joined
- Mar 4, 2021
- Messages
- 30
- Office Version
- 365
- Platform
- Windows
Hi Team,
I meet a problem to look up a value based on two criteria.
I have two tables on Sheet3, Tables 1 and 2. On Sheet2, I built up two dropdown lists based on content of Table 1 and headings of the Table 2 (Fig. 1). Table 1 has only two columns. Table 2 contains 8 columns, the 1st column has the heading of "Trap density", columns 2nd to 8th have headings of '4', '5', '6.67', etc and the values in the rest of the table are p(capture).
I want to look up a value in column B ("Trap density") of Table 2 based on the values in two dropdown lists and return the value in Cell E3 on Sheet2 (Fig. 2). For example, if I choose "Confidence level" for "Extremely low" in B3, C3 will show the range of "Expected p(capture)" value, say 0.20 - 0.30 in this case; based on the value of "Trap attractiveness(1/λ)" in D3, which is 10 for this situation, I want to look up a value of in column B named "Trap density" (B27: B38), which can meet the two criteria I set. In this situation is 25 in B28. How can I achieve this?
I know Vlookup can't do this because the values I am looking for are on the left side. I googled and got an idea of using Index and Match functions. However, I was screwed up because of the complexity of the multiple criteria and I new to these functions.
Thanks in advance.
Frank
I meet a problem to look up a value based on two criteria.
I have two tables on Sheet3, Tables 1 and 2. On Sheet2, I built up two dropdown lists based on content of Table 1 and headings of the Table 2 (Fig. 1). Table 1 has only two columns. Table 2 contains 8 columns, the 1st column has the heading of "Trap density", columns 2nd to 8th have headings of '4', '5', '6.67', etc and the values in the rest of the table are p(capture).
I want to look up a value in column B ("Trap density") of Table 2 based on the values in two dropdown lists and return the value in Cell E3 on Sheet2 (Fig. 2). For example, if I choose "Confidence level" for "Extremely low" in B3, C3 will show the range of "Expected p(capture)" value, say 0.20 - 0.30 in this case; based on the value of "Trap attractiveness(1/λ)" in D3, which is 10 for this situation, I want to look up a value of in column B named "Trap density" (B27: B38), which can meet the two criteria I set. In this situation is 25 in B28. How can I achieve this?
I know Vlookup can't do this because the values I am looking for are on the left side. I googled and got an idea of using Index and Match functions. However, I was screwed up because of the complexity of the multiple criteria and I new to these functions.
Thanks in advance.
Frank