Index and Match formula to be combined with if formula to return exact values.

[Thames17]

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.

[#2]

Rather than have us guess at this--it doesn't sound all that difficult--could you post your sheet, OR the IF function/formula that you've written that is not working. Somebody here might be able to detect the syntax error in what you've done.

[#3]

@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. 

[#4]

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

[#5]

HI@Twifoo 

It worked perfectly. Thank you very much. 

Cheers

Thames17. 

[#6]

@Thames17 , 

You're very much welcome!