Forum Discussion

Thames17's avatar
Thames17
Copper Contributor
Jan 29, 2020

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

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.

Formulas and Functions

5 Replies

Sort By
  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jan 30, 2020

    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

  • mathetes's avatar
    mathetes
    Silver Contributor
    Jan 30, 2020
    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.
    • Thames17's avatar
      Thames17
      Copper Contributor
      Jan 30, 2020

      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. 

Resources