Lookup First Non Blank Cell

[welshraz]

Hi,

I have a sheet with phone numbers, and on a separate sheet in the same workbook I have call data. What I need is a formula to search the Call Data sheet for the phone numbers from the Numbers sheet, and retrieve the information from the column (Dest_Init - column G) which shows when a call was first attempted for that particular number (i.e. 18/07/2016 18:04:21).

I have been using Vlookup, which in most cases is fine as an outbound call is made on the first attempt, but there are some cases when a call has been queued but the outbound call was never made so the cell is blank (in the Dest_Init column). I need to search for the first non-blank value for each phone number.

I hope this makes sense! I think I need an Index Match formula (maybe??) but I could do with some guidance. I have attached a sample, but the real data has many more rows and a few more columns with information that is not relevant to this query.

Thanks!

[DBY]

Hello
If there's only one occurrence of each phone number in column G, apart from the blank, then you could perhaps use SUMIFS, for example:

Please Login or Register  to view this content.

A2 is cell reference on the 'Numbers' sheet.

If the number doesn't exist then this will return zero but you could hide that with perhaps an IF function or a Custom cell formatting. This would hide zeros:

Please Login or Register  to view this content.

DBY

[Tony Valko]

Try this array formula** entered in B2:

=IFERROR(INDEX(Table1[Dest_Init],MATCH(1,(Table1[Numbers]=A2)*(Table1[Dest_Init]<>""),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as d/m/yyyy h:mm

Copy down as needed.