Selective VLookup 1

[SpeedThink]

I have the following:

Invc----Line No---Cd Type---Rej1 Cd--Mod1 Cd
66095---18---------Pymt-------------------------------
66095---12---------Pymt--------ELI----------------------
66095----1---------CH----------------------------26----
66149---18---------Pymt--------IDER---------------------
66149---12---------PYMT--------ADOO---------------------
66149----1---------CHG-----------------------------26----


I would like to use something similar to a vlookup to select the most recent rejection code (Rej1 Cd) for each invoice. For example, for invoice number 66095 - I would like to obtain the rejection code "ELI" not blank if I use a standard VLookup formula. Also, would like to obtain the mod code for each invoice which is only on the charge records without performing another VLookup.

Initially, I sorted by invoice number (ascending) and then by Line Number (descending) and performed a standard VLookup. Then, I realized that this will not work!


Thanks in advance.

SpeedThink

 

[SkipVought]

Hi,
[tt]
=INDEX(Rej1_Cd,SUMPRODUCT((Invc=L2)*(Rej1_Cd<>"")*ROW()),1)
[/tt]
where L2 contains the target invouce #

I used Named Ranges.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SpeedThink]

This does not work! It "pulls" data for the other invoices...

 

[PeterMoran]

Hi SpeedThink,

Here is a function called VLOOKUPSPCL that will do the first part of your query, assuming that you want the first nonblank code in col 4 for the nominated invoice no in your table, and may help in getting a result for your second part.

Function VLOOKUPSPCL(lookup_value, table_array As Range, _
col_index_num As Integer)
' Extension to VLOOKUP function. Allows for finding
' the first nonblank item that matches the lookup value.
Dim nRow As Long
VLOOKUPSPCL = "Not Found"
With table_array
For nRow = 1 To .Rows.Count
If .Cells(nRow, 1).Value = lookup_value Then
If .Cells(nRow, col_index_num).Text <> "" Then
VLOOKUPSPCL = .Cells(nRow, col_index_num).Text
Exit Function
End If
End If
Next nRow
End With
End Function

eg: =vlookupspcl(66095,Table,4) will give "ELI"

You could use the same function for part 2 -
- =vlookupspcl(66095,Table,5) assuming you wanted the first nonblank entry again.

If you need help setting up the function let us know.

Good Luck!

Peter Moran

 

[SpeedThink]

thanks.

This appears to be the solution that is needed. I will review in detail.