Skip to main content

How can I search a value that is between other two values?

  • June 10, 2016
  • 2 replies
  • 33 views
P

Hello.

I have a question about searching values in tables.

I have a quoted dollar amount for a customer in one table and, based on that value, I need to search for the discount in another table. How can I do that?

For example, if the quoted dollar amount in table A is $115, then look for the discount factor in table B that is between $100 and $120, and copy it to table A.

How can I search a value that is between other two values?

    This topic has been closed for replies.

    2 replies

    D
    Forum|alt.badge.img+4
    • Dean_Toland
    • Known Participant
    • July 18, 2016

    The only thing I can think of is to use nested IF statements in a formula. Have it check to see if the value is less than $120 but more than $100 and if so return the discount, if not leave it blank. This would get problematic if you have a table with a number of different ranges for discount points, and as I write this I’m assuming that’s what you have, is that accurate?

    P
    • Pablo_Marrero
    • Author
    • New Participant
    • July 18, 2016

    Hi Dean_Toland,

    Thank you for replying. Based on a suggestion from Victoria from Airtable’s customer service, I was able to solve my problem using a formula. It was a bit messy, but it does what I was trying to achieve. Here is the formula for the benefit of the community:

    Please note the bunch of right parenthesis at the end of the formula. This was the more problematic part. The formula gave errors until I got the correct number of parenthesis.

    IF({Seller Rating} <= 0, “”,IF(AND({Seller Rating} >= 0, {Seller Rating} <= 9), “ :medal_military: ”,IF(AND({Seller Rating} >= 10, {Seller Rating} <= 29), “ :medal_military: :medal_military: ”,IF(AND({Seller Rating} >= 30, {Seller Rating} <= 99), “ :medal_military: :medal_military: :medal_military: ”,IF(AND({Seller Rating} >= 100, {Seller Rating} <= 199), “ :medal_military: :medal_military: :medal_military: :medal_military: ”,IF(AND({Seller Rating} >= 200, {Seller Rating} <= 499), “ :medal_military: :medal_military: :medal_military: :medal_military: :medal_military: ”,IF(AND({Seller Rating} >= 500, {Seller Rating} <= 999), “ :gem: ”,IF(AND({Seller Rating} >= 1000, {Seller Rating} <= 1999), “ :gem: :gem: ”,IF(AND({Seller Rating} >= 2000, {Seller Rating} <= 4999), “ :gem: :gem: :gem: ”,IF(AND({Seller Rating} >= 5000, {Seller Rating} <= 9999), “ :gem: :gem: :gem: :gem: ”,IF(AND({Seller Rating} >= 10000, {Seller Rating} <= 19999), “ :gem: :gem: :gem: :gem: :gem: ”,IF(AND({Seller Rating} >= 20000, {Seller Rating} <= 49999), “ :crown: ”,IF(AND({Seller Rating} >= 50000, {Seller Rating} <= 99999), “ :crown: :crown: ”,IF(AND({Seller Rating} >= 100000, {Seller Rating} <= 199999), “ :crown: :crown: :crown: ”,IF(AND({Seller Rating} >= 200000, {Seller Rating} <= 399999), “ :crown: :crown: :crown: :crown: ”,IF({Seller Rating} >= 400000, “ :crown: :crown: :crown: :crown: :crown: ”,“Error”))))))))))))))))

    So, basically it uses a string of symbols to identify Sellers based on their rating on Aliexpress.com. This formula is used a part of a table used to research possible product suppliers on Aliexpress.

    I hope this helps if someone else needs to do a similar thing.

    Terms & ConditionsAccessibility statement