Jan 29 2020 03:05 PM
Hi
I am trying to obtain following values and the data are picked up using Index & Match formula from another sheet matching rows and columns. I tried the "If formula" combined with "Index & Match" but it seems not to work.
Original values | Should read as |
N-T | NONE |
FRE | NONE |
GST | G.S.T. |
Blank Cell | To have blank Cell. |
Thank you
Thames17.
Jan 29 2020 04:30 PM
Jan 29 2020 08:28 PM
@mathetesfollowing are the formulas.
The Index & Match formula pics data from the other sheet to Cell "AO4"...
INDEX('[Sheet1.xlsx]ITEM'!$B$5:$BO$1741,MATCH($A4,'[Sheet1.xlsx]ITEM'!$A$5:$A$1741,0),MATCH(AP$1,'[Sheet1.xlsx]ITEM'!$B$2:$BO$2,0))
And has to incorporate the below "IF" formula to obtain the correct text.
=IF(OR(AND(AO4="N-T"),AND(AO4="FRE")),"NONE",IF(AO4="","",IF(AO4="GST","G.S.T.","ERROR")))
Hope I have explained more logically this time to see the best way forward to get the correct text on AO4 cell.
Jan 29 2020 08:50 PM
Hi @Thames17,
Perhaps, this formula returns your desired result:
=IFERROR(LOOKUP(INDEX('[Sheet1.xlsx]ITEM'!$B$5:$BO$1741,
MATCH($A4,'[Sheet1.xlsx]ITEM'!$A$5:$A$1741,0),
MATCH(AP$1,'[Sheet1.xlsx]ITEM'!$B$2:$BO$2,0)),
{"","FRE","G.S.T","N-T"},
{"","NONE","GST","NONE"}),
"ERROR")
Cheers,
Twifoo
Jan 29 2020 09:18 PM
Jan 29 2020 09:40 PM
You're very much welcome!