CODEWITHSUNDEEP
google-sheetsspreadsheet
Kiame
Kiame

Reputation: 51

Filter row from an array if value is between two cells

I have a price table like the example below:

Price Table

I need to filter the table so depending on what i have on "m2 total" and "material" it returns the correct price. "m2 total" will be often values between two of the values on column A but sometimes it could be a number under 250 or above 2500.

In case G1 it's equal to any of the values on column A, I need it to filter the row with that value and the column with the correct material.

Thanks beforehand.

Upvotes: 0

Views: 72

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10806

=INDEX(
  B2:D6, 
  IFERROR(MATCH(G1, A2:A6, 1), 1), 
  MATCH(G2, B1:D1, 0))

IFERROR(MATCH(G1, A2:A6, 1), 1) Looks through the prices ascendingly (Uses the matching unit count or next smaller one), if it is under 250 it will fall back to the first row.

Upvotes: 2

Related Questions