Vlookup alteration
 

The vlookup function is traditionally used to search for a value in the
leftmost column of a table, and then returns a value in the same row from a
column you specify in the table.

However is it possible to tell vlookup to ignore the first value in a
column and use the second version and then return the value in the same row
in the next column.

I am interesting in using this as I have multiple versions of the same value
in a column

Thanks

Vlookup alteration
 

I don't know that VLOOKUP can be modified in that manner, however, you may
want to take a look at the Advanced Filter tool and see if it can help you.
It allows you to specify a value, which may repeat, and filter on that
criteria, and even copy the filtered results to another location entirely.

Here's some info on it: http://www.contextures.com/xladvfilter01.html

Dave

--
Brevity is the soul of wit.


"nir020" wrote:

The vlookup function is traditionally used to search for a value in the
leftmost column of a table, and then returns a value in the same row from a
column you specify in the table.

However is it possible to tell vlookup to ignore the first value in a
column and use the second version and then return the value in the same row
in the next column.

I am interesting in using this as I have multiple versions of the same value
in a column

Thanks

Vlookup alteration
 

Suppose the lookup value is in C1 and the table irange s A1:B100.
Try the array formula (by using Ctrl+shift+Enter to evaluate it):

=INDEX(B:B,SMALL(IF(A1:A100=C1,ROW(1:100)),2))


nir020 wrote:

The vlookup function is traditionally used to search for a value in the
leftmost column of a table, and then returns a value in the same row from a
column you specify in the table.

However is it possible to tell vlookup to ignore the first value in a
column and use the second version and then return the value in the same row
in the next column.

I am interesting in using this as I have multiple versions of the same value
in a column

Thanks

Vlookup alteration
 

Maybe you are looking for the code below?

Jack Sons
The Netherlands

-------------------------------------------------------------------------------------------------------------
'JLOOKUP v.1 Beta Function by JethroUK
'this function operates in a similar way to VLOOKUP for the first
'3 parameters, the fourth parameter "Ignore" is an optional search
'condition, which offers the user the ability to define WHICH
'occurance to read the results from.

'JDLOOKUP searches down the left-hand column of a range for a
'particular instance of a match, it then returns the value defined
'by the offset into the table

'Lookup - The data being sought, can be any value or cell content
'MyTable - Data range, including data to search and results
'Col% - The offset to the column containing the data results, must be
number.
'Ignore (Optional) The ammount of instances the search result must be
ignored,
'1 will stop at the 2nd occurance, 2 the 3rd etc. Default value is 0,
'stop at 1st occurance (as per VLOOKUP). Must be a number.


Function JLOOKUP(Lookup, MyTable, Col%, Optional Ignore%)
Dim i%

i% = Ignore%
JLOOKUP = "#N/B" 'was N#A

On Error Resume Next
MyRows% = UBound(MyTable, 1)
If MyRows% = 0 Then MyRows% = MyTable.Rows.Count
On Error GoTo 0
Application.ScreenUpdating = False
For y% = 1 To MyRows%
If Lookup = MyTable(y%, 1) Then
i% = i% - 1
If i% < 1 Then JLOOKUP = MyTable(y%, Col%): Exit For
End If

Next
Application.ScreenUpdating = True
End Function


'This Function must be inserted in an Excel Module (create one if ness),
'if it has been properly acknowledged by Excel, it will appear in the
'function list, as a user function.

-------------------------------------------------------------------------------------------------------------
"nir020" schreef in bericht
...
The vlookup function is traditionally used to search for a value in the
leftmost column of a table, and then returns a value in the same row from
a
column you specify in the table.

However is it possible to tell vlookup to ignore the first value in a
column and use the second version and then return the value in the same
row
in the next column.

I am interesting in using this as I have multiple versions of the same
value
in a column

Thanks