2

Here's my table:

Exchange No. Name Tier 30d Volume (higher than) Maker Taker Specials
1 FTX 1 $0 0.0200% 0.0700%
FTX 2 $2,000,000 0.0150% 0.0600%
FTX 3 $5,000,000 0.0100% 0.0550%
FTX 4 $10,000,000 0.0050% 0.0500%
2 Binance Regular User $0 0.0120% 0.0500%
Binance VIP 1 $15,000,000 0.0120% 0.0500%
Binance VIP 2 $50,000,000 -0.0100% 0.0500%

I want to retrieve the correct fees in another table as follows:

Volume (past 30d): volume variable, ie $10,000 FTX Binance
Column # 2 3 4
IN: Maker correct fee correct fee
OUT-stop: Maker correct fee correct fee
OUT-profit: Maker correct fee correct fee
OUT-manually: Maker correct fee correct fee

B3 cell (second table) should take the fee in cell E2 (of the first table). Why? Cause:

  1. C1 (of second table) says "FTX" as per column B row 2:5 (of first table)
  2. Volume (in B1 of second table) is higher than D2 but lower than D3 (of first table)
  3. B3 (of second table) says "Maker" which is column F (of first table)

So I tried to do a vlookup but only my criteria No. 3 would work with vlookup.

Other criteria are a range higher than (No. 1) and two different columns as "index" (in vlookup formula) which are, by the way, the searched text... (No. 2)

Someone has an idea to take into account those special criteria in vlookup, or similar, please?

Benjamin Perry
  • 75
  • 6
  • 1
    Make sure to add input and expected output as **text table** (NOT as IMAGE/LINK) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 24 '22 at 20:46
  • share a copy / sample of your sheet with an example of the desired output – player0 Oct 24 '22 at 20:51
  • 1
    @player0 I've just edited the post with a link to the sheet – Benjamin Perry Oct 24 '22 at 21:07
  • Btw, I cannot add the desired output to the sheet as I'm unsuccessful using the formula. But the desired output should be: N3 cell (from 10-2021 - Earnings) should take the fee in cell F5 (from Exchange Fees). – Benjamin Perry Oct 24 '22 at 21:12
  • @TheMaster I edited the question to replace screenshots with plain text tables and remove links to Google Sheets. Please re-open the question. Thanks. – Benjamin Perry Nov 04 '22 at 14:56
  • Voted to reopen. You need two more votes from the community. – TheMaster Nov 04 '22 at 15:07

1 Answers1

1

try:

=INDEX(VLOOKUP(M2, QUERY({'Exchange Fees'!B2:B&":", 'Exchange Fees'!D2:D, 
 FILTER('Exchange Fees'!A2:G, 'Exchange Fees'!A1:G1=M3)}, 
 "select Col2,Col3 where Col1 = '"&N2&"'", ), 2, 1))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Can you please explain the last line? I need to adapt it to an other sheet but do not understand this: "select Col2,Col3 where Col1 = '"&N2&"'", ), 2, 1)) – Benjamin Perry Nov 02 '22 at 10:40
  • @BenjaminPerry sure... `select Col2,Col3 where Col1 = '"&N2&"'", )` belongs to `QUERY` - we are selecting 2nd and 3rd column eg. `'Exchange Fees'!D2:D` and one of `'Exchange Fees'!A2:G` where Column 1 equals `N2` then , `2, 1)` belongs to `VLOOKUP` and the meaning is "output 2nd column if the match is greater or equal – player0 Nov 02 '22 at 12:41
  • thanks but I didn't get it all... You say you select "2nd and 3rd column eg. `'Exchange Fees'!D2:D` [...]" but D is not the 2nd column, nor 3rd... It's the 4th column. Then you say `2, 1)` "output 2nd column if the match is greater or equal" but what is greater or equal? How does this code return the value in columns E & F? (these are the columns I need to adapt in my other sheet). I really appreciate any help you can provide. – Benjamin Perry Nov 02 '22 at 13:37
  • Even when you say "`'Exchange Fees'!A2:G` where Column 1 equals `N2`" column 1 is A, though N2 value is in column B, so column 2. There's something I'm missing here... – Benjamin Perry Nov 02 '22 at 15:38
  • 1
    @BenjaminPerry we start with FILTERing range A2:G if headers in A1:G1 match M3. the output of this FILTER formula should be 1 column. than we take this column from FILTER and add it in array where Col1 = B column, Col2 is D column and Col3 is our column from FILTER. next we perform QUERY. output from the QUERY are two columns: first column is Col2 (eg D column) and second column is Col3 (eg column from FILTER) then we use VLOOKUP where we search M2 in first column of QUERY and we output the 2nd column of QUERY. VLOOKUP is able to function in two modes: – player0 Nov 02 '22 at 16:36
  • 1
    @BenjaminPerry if 4th VLOOKUP parameter is set to 0, vlookup will output only exact matches. if 4th argument of VLOOKUP is set to 1, vlookup can output "approximate matches within sorted data" - this will fork only for numeric values so for example if we looking for number 5 within array of 3, 8, 10 then the output will be 3. if we would look for 8 within same 3, 8, 10 array the output will be 8. and this way if we continue to look for 9 the output will be 8 etc. so its either exact match or lowest level – player0 Nov 02 '22 at 16:36
  • Thanks for this thorough explanation! We now encountered another issue which my colleague is pointing out here: https://stackoverflow.com/questions/74306613/sheets-excel-inside?noredirect=1# – Benjamin Perry Nov 04 '22 at 09:14