Vlookup?? query??? Suggestions?

user_1330742 0
Viewed 63

I have the following Google Sheet.

[1]: [https://docs.google.com/spreadsheets/d/1q9I7XhyEGKeAk93mDyiXpP95Kc9qmiPfvnhEOcTwbIU/edit#gid=0]

Image of Google Sheet I could edit the fields in green (A16, B16), with A16 being a data validation drop-down list and B16 being a typed-in number.

Using vlookup, I can get the Base Value without any issues.

=VLOOKUP(A16,A2:B12,2, false)

What I can not figure out is how to get the factor value. For example, I have select H, which is row #9. As the size value is 1.62, which is greater than the Mid Size but less than the Max Size for that row, I want to return the Factor 2 value of 1.5.

I have tried multiple vlookup / query codes, but all don't work.

The Sum is just Base * Factor to give a final value. Ideally, I would select the lookup, enter a size and it only shows the Sum value.

google-sheets-formula
2 Answers

This definitely is NOT the most efficient way, but hopefully it works:

=IF(A16=A2,IF(B16>B2,"1.62",C2),=IF(A16=A3,IF(B16>B3,"1.62",C3),=IF(A16=A4,IF(B16>B4,"1.62",C4),=IF(A16=A5,IF(B16>B5,"1.62",C5),=IF(A16=A6,IF(B16>B6,"1.62",C6),=IF(A16=A7,IF(B16>B7,"1.62",C7),=IF(A16=A8,IF(B16>B8,"1.62",C8),=IF(A16=A9,IF(B16>B9,"1.62",C9),=IF(A16=A10,IF(B16>B10,"1.62",C10),=IF(A16=A11,IF(B16>B11,"1.62",C11),=IF(A16=A12,IF(B16>B12,"1.62",C12),"ERROR")))))))))))
user_175547280

Probably not the best way to handle this, but it does work. Partially cause my base factor value is 1, so multipling the value by 1, does not change anything

=(IFNA(QUERY(A2:H12,"select D where (A = '"&A16&"' AND (C <= "&B16&" AND E > "&B16&")) ORDER BY A"),1)*IFNA(QUERY(A2:H12,"select F where (A = '"&A16&"' AND (E < "&B16&" AND G >= "&B16&")) ORDER BY A"),1)*IFNA(QUERY(A2:H12,"select H where (A = '"&A16&"' AND (G <= "&B16&")) ORDER BY A"),1))
user_13307420
Related