I would like to return a value 4.42295, if i choose MYR. If I choose SGD will return 1.32963 as picture show below. I had tried some formula in the picture but seem like cannot solve what i need. Thank for helping me.
1 Answer
If I have understood correctly, you need to use the following formula in order to accomplish the desired output:
=IFERROR(INDEX($C$7:$E$19,MATCH(1,(G2>=A7:A19)*(G2<=B7:B19),0),MATCH(I2,C6:E6,0)),0)
Or, If using MS365 then use XLOOKUP()+FILTER() or Double XLOOKUP()
=XLOOKUP(1,(G2>=A7:A19)*(G2<=B7:B19),FILTER(C7:E19,C6:E6=I2,""),"")
Or,
=XLOOKUP(1,(G2>=A7:A19)*(G2<=B7:B19),XLOOKUP(I2,C6:E6,C7:E19))
However, from the screenshot in OP, it seems you are using Google-Sheets if so then use the following formula:
=IFNA(FILTER(XLOOKUP(I2,C6:E6,C7:E19,0),(G2>=A7:A19)*(G2<=B7:B19)),0)
answered Feb 11 at 21:19
-
1
-
hi Mayukh Bhattacharya, wondering how to make this Filter XLookup working with Arrayformula? Commented Feb 18 at 5:28
-
@AnakinTeow but why do you need it with
ARRAYFORMULA()when it is already working without it Commented Feb 18 at 6:23 -
1Hi Mayukh, attached with the google sheet link docs.google.com/spreadsheets/d/… Commented Feb 26 at 14:44
-
1
INDEXfunction, row number first, then column number.