StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Then try this:</p> <pre><code>Function FLOOKUP(lookup_value, table_array As Range, col_index_num As Long, _ range_lookup As Boolean, Optional ref_value, Optional criteria) As Variant Dim FoundCell As Range Dim LastCell As Range Dim FirstAddr, find_value As String Dim my_range As Range Dim row_count, col_count As Long Dim check As Boolean col_count = table_array.Columns.Count find_value = lookup_value If col_index_num >= 0 Then Set my_range = table_array.Resize(, 1) Else Set my_range = table_array.Resize(, 1).Offset(0, col_count - 1) End If With my_range row_count = .Cells.Count If row_count = 1048576 Then row_count = .Cells(.Cells.Count).End(xlUp).Row End With Set my_range = my_range.Resize(row_count) Set LastCell = my_range.Cells(my_range.Cells.Count) If range_lookup Then Set FoundCell = my_range.Find(what:=find_value, after:=LastCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Else Set FoundCell = my_range.Find(what:=find_value, after:=LastCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) End If If Not FoundCell Is Nothing Then FirstAddr = FoundCell.Address If IsNumeric(col_index_num) And Abs(col_index_num) <= col_count Then Select Case col_index_num Case Is > 0 If IsMissing(ref_value) Then FLOOKUP = FoundCell.Offset(0, col_index_num - 1).Value Else If ref_value = criteria Then FLOOKUP = FoundCell.Offset(0, col_index_num - 1).Value Else FLOOKUP = CVErr(xlErrNA) Exit Function End If End If Case Is < 0 If IsMissing(ref_value) Then FLOOKUP = FoundCell.Offset(0, col_index_num + 1).Value Else If ref_value = criteria Then FLOOKUP = FoundCell.Offset(0, col_index_num + 1).Value Else FLOOKUP = CVErr(xlErrNA) Exit Function End If End If End Select Exit Function Else FLOOKUP = CVErr(xlErrRef) Exit Function End If Else FLOOKUP = CVErr(xlErrNA) Exit Function End If End Function </code></pre> <p>Still needs refining but i how this gets you started.<br></p> <p><strong>SYNTAX:</strong></p> <p>FLOOKUP (lookup_value, table_array, col_index_num, range_lookup, [ref_value], [criteria])</p> <p>The first four argument is same as <code>Vlookup</code> but with <code>range_lookup</code> not optional.<br> The remaining two(2) is optional.<br> <code>ref_value</code> is the value you wish to compare to (in your case values found in Column B).<br> <code>criteria</code> is the test criteria. (in your case 0)</p> <p><strong>Here's the screen shot:</strong> <img src="https://i.imgur.com/fxHQBpH.png" alt="sample image"> <br> <br></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload