Log In
Register
Home
Power Query
October 7, 2021
Offline
Hi,
I would like to return a value from a table based on 2 columns.
The file I uploaded has an example in the "Values" sheet.
Basically, upon entering the Nozzle and the Bar values in table "Calc", the relevant Vol/hr should be returned from table "Values".
Example: Nozzle=Brown; Bar=5.5 the formula should find the value "30".
Tried with Index and with XLookup but couldn't figure it out.
Any help will be appreciated.
Regards.
Ayal Telem.
See Answer
Trusted Members
February 13, 2021
Offline
I got it with an index/sumproduct, since sumproduct can handle arrays.
=INDEX(Values[Vol/hr],SUMPRODUCT((([@Nozzle]=Values[Nozzle])*([@Bar]=Values[Bar]))*(ROW(Values[Bar])-1)))
See the worksheet attached. 🙂
Answers Post
Trusted Members
Moderators
January 31, 2022
Offline
Since you mentioned having tried XLOOKUP, you should also have FILTER. So, try this as an alternativ:
=FILTER(Values[Vol/hr],(Values[Nozzle]=[@Nozzle])*(Values[Bar]=[@Bar]))
1 Guest(s)
