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.
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. 🙂
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]))
Thank you very much Jessica and Riny.
Both options seems valid. I'll study them and will learn something new in the process of implementing them.
Ayal Telem.
