Desired outcome:
I would like to return a value in column C (column D is the values I'd like the formula to return) - the value in column C should return on the criteria it matches the product name in column A and is in between the price ranges in column F.
Problem:
I can't figure out a formula which would achieve this outcome - or if it is even possible - I have included the spreadsheet link below:
You can now check out the sheet:
It is possible but you have to make some changes to the data placement for it to work. Also the number should not have the string '£', but instead apply a number formatting that doesn't interfere with the number but displays it with the custom formatting:
For instance the combination of 0.1-0.99 as a range isn't flexible if a function is to be used to check a range.
So I've introduced a min and max range where based on the EXCEL =LOOKUP() function, it would work well, even though it only uses the minimum values:
=LOOKUP(lookup_value, lookup_vector, [result_vector])
As long as the range of minimum values has been provided, it will work as though the minimum and max values are provided since the next minimum value is used as the max value of the previous value.
Then now the function, specifically on the first data:
=IF(A2=F1,LOOKUP(B2,F2:F4,D2:D4),LOOKUP(B2,I2:I4,D2:D4))
The function checks whether the value in the product column corresponds to the title in the F column, which is socks, if it corresponds, then perform a lookup for the price in that cell: B2, and check whether it is in the range of values provided in the lookup_vector and the results vector have also been provided with a limit of the first 3 rows containing the socks products.
The "ELSE" part of the IF clause is the third part of the section, which now performs a lookup, but with the lookup_vector being the column with the title shorts, and the results vector limited to the products with the shorts.
The reason the results vector is being limited, is because there are prices which are same, for socks and shorts, so the lookup will return the first value it encounters, therefore a range is supplied.
