Menu
I am using the following formula:
=IFERROR(INDEX('Cleaned Post'!W:W,MATCH(Combined!$C2,'Cleaned Post'!$C:$C,0))," ")
This formula is working beautifully, except that for blank cells, it's returning "0". I would like blank cells to be return as blank.
Specifically, this is what I have
Sheet 1 (entitled Cleaned Post)
Name Email Age Gender Task #1
Andrew [email protected] 18 1 80
Jason [email protected] 20 1 95
Judy [email protected] 18 2 __
Jack [email protected] 24 1 65
Sheet 2 (entitled Combined) - What I'm getting
Email Task#1
[email protected] 80
[email protected] 95
[email protected] 0
[email protected] 65
Sheet 2 (entitled Combined) - What I want
Email Task#1
[email protected] 80
[email protected] 95
[email protected] __
[email protected] 65
What do I need to do to adjust this formula?
519
Example 3 – Return 0 in case of an Error If you don't specify the value to return by IFERROR in the case of an error, it would automatically return 0. For example, if I divide 100 with 0 as shown below, it would return an error.
IFERROR INDEX MATCH We can use IFERROR with the combination of INDEX and MATCH functions (as used in the VLOOKUP function). INDEX and MATH Lookup functions returns an error value (#NA) if the lookup value is not found the lookup range. We can use IFERROR function to produce the alternative string.
Copy that cell (Ctrl+C), then select cells from C2 to C7 and use the 'Go to special' to select Blank cells only under Home tab, in Editing > Find & Select > Go To Special. Simply just Paste the formula to all the blank cells by Ctrl+V. You will get all the information in one row on the first row for each companies.
What sort of values is your formula returning? If they are text values it's sufficient to concatenate a "null string" to your INDEX/MATCH formula like this:
=IFERROR(INDEX('Cleaned Post'!W:W,MATCH(Combined!$C2,'Cleaned Post'!$C:$C,0))&"","")
That also works for numbers except it will convert them to text so if you don't want that you can try this version:
=IFERROR(IF(INDEX('Cleaned Post'!W:W,MATCH(Combined!$C2,'Cleaned Post'!$C:$C,0))="","",INDEX('Cleaned Post'!W:W,MATCH(Combined!$C2,'Cleaned Post'!$C:$C,0))),"")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
