 Highlighted

# 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 values Should read as N-T NONE FRE NONE GST G.S.T. Blank Cell To have blank Cell.

Thank you

Thames17.

5 Replies
Highlighted

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

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

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

@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

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

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

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

HI@Twifoo

It worked perfectly. Thank you very much.

Cheers

Thames17.

Highlighted

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

You're very much welcome!