Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Addition to existing match index formula that will leave cells blank

0

I am using a match index formula to bring over data from the worksheet titled "920" to the "Data" worksheet.  I have tried several different additional to my formula to get columns D, F and G to be left blank if there is no data to bring over.  The data in the 920 worksheet will be updated quarterly, which will then update the DATA worksheet.

Attached is a sample worksheet.

Answer
Discuss

Answers

0
Selected Answer

You are outputting a 0 (zero) when no result is found when you should output nothing, like this ""

Here is the formula from cell F2:

=IF(ISNA(MATCH(B2,'920'!$D:$D,0)),0,INDEX('920'!J:$J,MATCH(B2,'920'!$D:$D,0)))

Here is the updated version:

=IF(ISNA(MATCH(B2,'920'!$D:$D,0)),"",INDEX('920'!J:$J,MATCH(B2,'920'!$D:$D,0)))

I put in bold the only change so it should be easier to see; do this with all of the formulas and it should be no problem.

Going forward, you might want to use the IFERROR function. You could then use a formula in cell F2 like this:

=IFERROR(INDEX('920'!J:$J,MATCH(B2,'920'!$D:$D,0)),"")
Discuss
0

If I understand you correctly you would like to show a blank cell if the referenced cell is blank. You may like to try this formula.

[CODE=IF(ISNA(MATCH(B2,'920'!$D:$D,0)),0,IF(LEN(INDEX('920'!$C:$C,MATCH(B2,'920'!$D:$D,0))),INDEX('920'!$C:$C,MATCH(B2,'920'!$D:$D,0)),"No name"))[/CODE]

It will return "no match" if no match was found for B2 and "no name" if the match was found but there is nothing in that cell. You can repalce either placeholder with a simple "" to have a blank cell in any event.

As an alternative you might like to use the next formula which I tested in D3 of your worksheet.

=IF(C3=0,"no match", IF(INDEX('920'!$C:$C,MATCH(B3,'920'!$D:$D,0))="","no name", INDEX('920'!$C:$C,MATCH(B3,'920'!$D:$D,0))))

This formula Dispenses with the ISNA function. Instead, it relies on the result of the ISNA function in column C. Otherwise it is the same as the first solution except that the first solution looks for a string of zero length whereas the second insists on "". These are two ways of testing for the same thing with identical results.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login