CODEWITHSUNDEEP
sortinggoogle-sheetsgoogle-sheets-formulavlookupgoogle-query-language
dede.brahma
dede.brahma

Reputation: 329

Google Sheets - Find and match value with filter

I have try using query, vlookup and filter to get data from other tab sheets

I don't know how to get data with logic condition.

Here my sheets :

https://docs.google.com/spreadsheets/d/1sukCRlU1UuXS6IaR8xCYs8QScH4B2m60bqsrypDkzTM/edit?usp=sharing

Step 1 :

I have tab sheet with list of reward

enter image description here

Step 2 :

in the other tab sheet (cust_point), in column C i want to get value from tab sheet Reward where it use match and logic condition >= and <=. Then here my expectation :

enter image description here

Problem :

First, i try with formula

=QUERY(Reward!A2:B, "SELECT A WHERE B <= '"&C2&"'", 1)

then the result just showed but doesn't match

Second, i try with formula

=FILTER(Reward!A2:B,C2>=Reward!B2:B)

Third, i try with formula

=IF(VLOOKUP(C2,Reward!B2:B,1,FALSE), C2>=Reward!B2:B)

They all formula doesn't get value with match data like my expectation

How can I achieve that?

Upvotes: 1

Views: 287

Answers (1)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(IFNA(VLOOKUP(10000-B2:B; 
 SORT({10000-Reward!B2:B\Reward!A2:A}); 2; 1)))

enter image description here

Upvotes: 2

Related Questions