"IF" to "IFERROR" (or other formula) help by skipping empty cells

Hi all

Background:
I've been trying to get excel to skip empty cells within the row so that it brings up the next TRUE value. Currently my table of info is like this (on sheet1):

B C D E
Date (B) Receiver/Payee (C) Method (D) Amount (E)
01/08/2013 (B) JE (C) cash (D) £29.70 (E)

Currently formula (on sheet2):
=IF(Sheet1!C3="JE", Sheet1!E3,"")

Problem:
This shows all FALSE values as empty cells.

Request:
I've searched for quite a while and saw some people give examples using other types of formulae which I've tried but without success at tailoring it to my need. I would like the final formula to show the Date, Method, and Amount if arguments are TRUE (each in a separate column) without any blanks on a new sheet.

Thanks in advance and feel free to ask me for clarifications

Hi, and welcome to the forum.

Upload your workbook and manually add some examples of your expected results, with notes if it's not blindingly obvious how you've arrived at them.

Hi,

Assuming your source table data begins in row 2 (with headers in row 1) and ends in row 100, enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER), and copy down and across to the right as required:

=IFERROR(INDEX(Sheet1!$B$2:$E$100,SMALL(IF(Sheet1!$C$2:$C$100="JE",ROW(Sheet1!$C$2:$C$100)-MIN(ROW(Sheet1!$C$2:$C$100))+1),ROWS($1:1)),COLUMNS($A:A)),"")

Obviously amend those ranges to suit your actual requirements.

Regards

@Richard Buttrey

You don't appear to be accepting private messages, so I will apologise in this thread for having posted my reply to the OP. I was working on the solution whilst you posted so I did not see your request.

Regards

You're welcome.

XOR Richard only asked ror a sample workbook, the OP did not "break" any forum rules, so you were perfectly fine in responding to the question as you did

Cheers, FDibbins (back already, eh? ). Thought I might've been in breach of Rule 7 (?), so just wanted to let Richard know.