VLookup + Identify non-blank cells

[elw2125]

Hi,

I am trying to create a vlookup that will pull only the non-blank cells out of a column. Column A for instance may contain 5 blank cells and one cell that contains text. I would like the vlookup to find just the cell with text.

I tried using "*" and "?" but could not seem to find a solution that worked.

Any help would be appreciated.

Thanks!

 

[BeingYogi]

can u just provide sample data

 

[elw2125]

A1 (blank)
A2 (blank)
A3 "FASTXYZ001"
A4 (blank)
A5 (blank)
A6 "FASTABC005"

I would like the vlookup to pull out "FASTXYZ001" and "FASTABC005" from the column (ignoring the blank cells). Initially, I tried to do a vlookup searching for the text "FAST" but that was not working. Searching for a non-blank may be simpler.

Thanks

 

[BeingYogi]

there's INDEX and MATCH option, but you can provide some details more, will be more helpful

 

[elw2125]

I don't think either of those will work for me. I have several tabs with data in the columnar format below and I basically want to consolidate into a single tab any cell that contains text that reads "FAST...". So, the vlookup would search each tab and return the "FAST..." text to one tab in a single column.

A1 (blank)
A2 (blank)
A3 "FASTXYZ001"
A4 (blank)
A5 (blank)
A6 "FASTABC005"

Is this clearer? I apologize for any confusion.