look up a value based on criteria

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. 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
 

Attachments

  • Lookup_a_value.JPG
    Lookup_a_value.JPG
    233.4 KB · Views: 12

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,390
Given this as Sheet3:

Book1
ABCDEFGHI
14Table1
15Confidence levelSuggested p(capure)
16Extremely low0.20 - 0.30
17Very low0.31 - 0.40
18Low0.41 - 0.50
19Average0.51 - 0.60
20Above average0.61 - 0.70
21High0.71 - 0.80
22Very high0.81 - 0.90
23Extremely high0.91 - 1.00
24
25Table 2
26Trap density456.671013.32033.3
27160.17
28250.26
29360.37
30490.46
31640.56
32810.65
331000.73
341210.8
351440.85
361690.9
371960.93
382250.95
Sheet3


Notice 1) that I didn't type in everything. Next time consider using the XL2BB tool which would allow the helpers to copy your data directly without having to retype everything from a picture. The link is in my signature or the reply box. It's easy to install and use. and 2) that I changed "Extremely high" to 0.91 - 1.00.

Then you can do this with Sheet2:

Book1
ABCDE
1Choose base trap density based
2Confidence levelExpected p(capture)Trap atractiveness (1/^)(m)Trap density (No./mi2)
3Extremely low0.20 - 0.301025
Sheet2
Cell Formulas
RangeFormula
C3C3=VLOOKUP(B3,Sheet3!$B$16:$C$23,2,0)
E3E3=INDEX(Sheet3!$B$27:$B$38,MATCH(RIGHT(C3,4)+0,INDEX(Sheet3!$C$27:$I$38,0,MATCH(D3,Sheet3!$C$26:$I$26))))
 
Solution

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,390
I just noticed you have Excel 365. This also may work for you in E3:

Excel Formula:
=XLOOKUP(RIGHT(C3,4)+0,FILTER(Sheet3!C27:I38,Sheet3!C26:I26=D3),Sheet3!B27:B38,,-1)
 

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi Eric,
Thank you so much for your response. I have tried and both answer are working. Thank you for your help.
I need a little bit of time to digest your solutions.

Frank
 

Forum statistics

Threads
1,140,999
Messages
5,703,645
Members
421,307
Latest member
morrden86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top