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

%3CLINGO-SUB%20id%3D%22lingo-sub-1138477%22%20slang%3D%22en-US%22%3EIndex%20and%20Match%20formula%20to%20be%20combined%20with%20if%20formula%20to%20return%20exact%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1138477%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20obtain%20following%20values%20and%20the%20data%20are%20picked%20up%20using%20Index%20%26amp%3B%20Match%20formula%20from%20another%20sheet%20matching%20rows%20and%20columns.%20I%20tried%20the%20%22If%20formula%22%20combined%20with%20%22Index%20%26amp%3B%20Match%22%20but%20it%20seems%20not%20to%20work.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EOriginal%20values%3C%2FTD%3E%3CTD%3EShould%20read%20as%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EN-T%3C%2FTD%3E%3CTD%3ENONE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFRE%3C%2FTD%3E%3CTD%3ENONE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGST%3C%2FTD%3E%3CTD%3EG.S.T.%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBlank%20Cell%3C%2FTD%3E%3CTD%3ETo%20have%20blank%20Cell.%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EThames17.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1138477%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1138583%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20Match%20formula%20to%20be%20combined%20with%20if%20formula%20to%20return%20exact%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1138583%22%20slang%3D%22en-US%22%3ERather%20than%20have%20us%20guess%20at%20this--it%20doesn't%20sound%20all%20that%20difficult--could%20you%20post%20your%20sheet%2C%20OR%20the%20IF%20function%2Fformula%20that%20you've%20written%20that%20is%20not%20working.%20Somebody%20here%20might%20be%20able%20to%20detect%20the%20syntax%20error%20in%20what%20you've%20done.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1138849%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20Match%20formula%20to%20be%20combined%20with%20if%20formula%20to%20return%20exact%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1138849%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3Efollowing%20are%20the%20formulas.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Index%20%26amp%3B%20Match%20formula%20pics%20data%20from%20the%20other%20sheet%20to%20Cell%20%22AO4%22...%3C%2FP%3E%3CP%3E%3CFONT%3EINDEX('%5B%3CFONT%3ESheet1%3C%2FFONT%3E.xlsx%5DITEM'!%24B%245%3A%24BO%241741%2CMATCH(%24A4%2C'%5BSheet1.xlsx%5DITEM'!%24A%245%3A%24A%241741%2C0)%2CMATCH(AP%241%2C'%5B%3CFONT%3ESheet1%3C%2FFONT%3E.xlsx%5DITEM'!%24B%242%3A%24BO%242%2C0))%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20has%20to%20incorporate%20the%20below%20%22IF%22%20formula%20to%20obtain%20the%20correct%20text.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3DIF(OR(AND(AO4%3D%22N-T%22)%2CAND(AO4%3D%22FRE%22))%2C%22NONE%22%2CIF(AO4%3D%22%22%2C%22%22%2CIF(AO4%3D%22GST%22%2C%22G.S.T.%22%2C%22ERROR%22)))%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EHope%20I%20have%20explained%20more%20logically%20this%20time%20to%20see%20the%20best%20way%20forward%20to%20get%20the%20correct%20text%20on%20AO4%20cell.%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1138865%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20Match%20formula%20to%20be%20combined%20with%20if%20formula%20to%20return%20exact%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1138865%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F537869%22%20target%3D%22_blank%22%3E%40Thames17%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%2C%20this%20formula%20returns%20your%20desired%20result%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(LOOKUP(INDEX('%5BSheet1.xlsx%5DITEM'!%24B%245%3A%24BO%241741%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EMATCH(%24A4%2C'%5BSheet1.xlsx%5DITEM'!%24A%245%3A%24A%241741%2C0)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EMATCH(AP%241%2C'%5BSheet1.xlsx%5DITEM'!%24B%242%3A%24BO%242%2C0))%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%7B%22%22%2C%22FRE%22%2C%22G.S.T%22%2C%22N-T%22%7D%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%7B%22%22%2C%22NONE%22%2C%22GST%22%2C%22NONE%22%7D)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22ERROR%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3ETwifoo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1138892%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20Match%20formula%20to%20be%20combined%20with%20if%20formula%20to%20return%20exact%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1138892%22%20slang%3D%22en-US%22%3E%3CP%3EHI%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20worked%20perfectly.%20Thank%20you%20very%20much.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3EThames17.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1138915%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20Match%20formula%20to%20be%20combined%20with%20if%20formula%20to%20return%20exact%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1138915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F537869%22%20target%3D%22_blank%22%3E%40Thames17%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20very%20much%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 valuesShould read as 
N-TNONE
FRENONE
GSTG.S.T.
Blank CellTo have blank Cell.

 

Thank you

Thames17.

5 Replies
Highlighted
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.
Highlighted

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

Highlighted

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

Highlighted

HI@Twifoo 

 

It worked perfectly. Thank you very much. 

 

Cheers

Thames17. 

Highlighted