# Help with formula: VLOOKUP / INDEX/ MATCH

#### belbezub

##### New Member
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>

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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),""))

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.

Welcome to MrExcel.

Just an alternative:

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

Replies
3
Views
686
Replies
3
Views
394
Replies
8
Views
467
Replies
1
Views
275
Replies
5
Views
394

1,221,200
Messages
6,158,492
Members
451,497
Latest member
Marese

### 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.

### Which adblocker are you using?

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

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