Help with formula: VLOOKUP / INDEX/ MATCH

[belbezub]

Hi everybody, I have read some threads in order to solve my problem, but I wasnt successfull.
So here it is.

I have one value in G15 (which has two options „yes“ and „no“
I have another value in G20 (which is plain random number)
I have table which has columns:
A (which consists of „yes“ and „no“)
B and C column are number ranges
Column D should be return value.

First I should look if it it matches A column, then it should find under which range number from G20 belong to (number ranges are in column B and C ), and then it should return value from D (last column)

I have tried some combinations with INDEX and MATCH but I wasnt successfull.

Thank you for helping me


e.g.
G15= YES
G20= 115

Return value should be: 3,5%

e.g.
G15= NO
G20= 115
Return value should be 3%

A	B	C	D
Yes	86	100	1,50%
Yes	101	110	2,50%
Yes	111	120	3,50%
Yes	121	130	7,00%
No	91	100	1,00%
No	101	110	2,00%
No	111	120	3,00%
No	121	130	6,00%

<tbody>
</tbody>

 

[Oeldere]

this is one solution.

=if($G$15="Yes",VLookup($G$20,$B$1:$D$4,3,1),if($G$15="No",Vlookup($G$20,$B$5:$D$8,3,1),""))

 

[gsistek]

Try using
=SUMIFS(D:D,A:A,G15,B:B," < ="&G20,C:C," > ="&G20)
Even though this returns the sum, it looks like there should only ever be one row that meets the criteria.

Note: Remove the spaces around the < and > signs.

 

[Andrew Poulsom]

Welcome to MrExcel.

Just an alternative:

=INDEX(D1:D8,MATCH(1,INDEX((A1:A8=G15)*(B1:B8<=G20)*(C1:C8>=G20),),FALSE))