Query look up to a specific column

Ask for Help
1

I have a tarrif sheet, that states the cost of a container between different cities, based on size.

image
image712×186 29.2 KB

When doing a quote, the customer will select 3 variables (FROM, TO, size), how do I look up the correct price column, without having to create 3 rows for each row I have (one for each size).

What I tried:
I did a “QUERY” then based on FROM and TO, then I did 3 different “lookup” columns for each size.
I was then going to do an IF-THEN and say if size includes 20 THEN “lookup20” IF…

BUT when I do this, I do not get the option of selctive the “lookup20” column

2

When you do a Lookup through a Query, the result is always an array. What you should use instead is 3 Single Value columns.

That said, this whole thing would be much simpler if you normalised your Tarrif table. That is, you should have 3 columns - one each for From, To & Size. And then one row for each combination. There will be more rows in your Tarrif table, but it will be much easier to maintain and work with.

3

Hi Darren,

As always greatly appreciate the insights and guidance. I will do a 3 column table.

I was thinking to redoing the table as suggested, so wanted to make sure I was not missing something.