How to return value blank cell, if criteria do not match

**devawad** · 11-25-2013, 04:23 AM

Hi all,

=OFFSET('Stock Details'!$C$1,MATCH("PABW0608040",'Stock Details'!$C$2:$C$114,0),5)

I am using the formula above which works just fine, however it returns a #N/A when the criteria do not match

I would like the formula to return a blank cell.

plz help

Dev.

**benishiryo** · 11-25-2013, 04:35 AM

hi Dev. OFFSET is slightly volatile & that means it might slow down your workbook. you can try INDEX instead:
=INDEX('Stock Details'!H:H,MATCH("PABW0408040",'Stock Details'!C:C,0))

i am not too sure about your rationale here with the lookup_value though. do you need to manually type in each cell? why can't you use C5? if it's because of additional spaces in column C, use TRIM:
=INDEX('Stock Details'!H:H,MATCH(TRIM(C5),'Stock Details'!C:C,0))

to hide the error, surround it with IFERROR. same thing if you still wish to use OFFSET:
=IFERROR(INDEX('Stock Details'!H:H,MATCH(TRIM(C5),'Stock Details'!C:C,0)),"")

**devawad** · 11-25-2013, 05:21 AM

Dear Forum Guru,

You are just amazing, you are true forum guru, All the solution you have given are working exactly as I want.

I am very proud to be a part of excel help forum and having a Guru like you...

Hat's of to you..

Dev.