Retrieve country and volume dependent price

this would be one way...
=IF(C15<101,C15*VLOOKUP($B15,$B$4:$C$8,2,FALSE),IF(C15<201,C15*VLOOKUP($B15,$B$4:$D$8,3,FALSE),C15*VLOOKUP($B15,$B$4:$E$8,4,FALSE)))
in O15, dragged down and across.

I am curious what combinations of IF(), MATCH(), and INDEX() you tried. It might help us help you understand better what you were doing wrong.

Here's the approach I would probably use (only needs INDEX() and two MATCH() functions).

1) Enter 0, 101, 201 into a helper row adjacent to the lookup table (I used row 10 -- 0 in C10, 101 in D10, 201 in E10).
2) To get the row number, a MATCH() function referencing the country name MATCH($B15,$B$4:$B$8,0). Note the mix of relative and absolute references. Also note that the match type argument is 0 to indicate exact match (If you are unfamiliar with the behavior of the match_type argument, help file here: https://support.office.com/en-us/art...9-533f4a37673a ).
3) To get the column number, I use a MATCH() function referencing the volumes. MATCH(C15,$C$10:$E$10,1). Note that I used 1 for the match_type argument to get the approximate match behavior. I know that almost all lookup examples across the internet demonstrate exact match type behavior and almost none of them demonstrate the approximate match behavior. I recommend you spend some time understanding how the approximate match behavior works, because I find it very useful in cases like this.
4) With row and column number, a simple INDEX() function will extract the unit price INDEX($C$4:$E$8,MATCH(step 2),MATCH(step 3))

Here another way.

Another option with simple VLOOKUP:

Please Login or Register  to view this content.

Or:

Please Login or Register  to view this content.

With 10000000 is any number that is large enough.

@Popipipo - Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

Another way.

Formula:

Please Login or Register  to view this content.