CODEWITHSUNDEEP
excel
thephatp
thephatp

Reputation: 1489

Excel formula: For each instance of value in column, get value of another column in same row

I am looking to solve the following problem in Excel:

ID     Key     Value
1      10      20
2       5      30
3      10      20
4      10      20

If key == 10 and Value == 20, get the ID.

So, I need this to produce the following list: "1,3,4"

Essentially, I'm looking to see if one value is in a given range, and another value is in another range, give me the corresponding value (same row) in another range.

I cannot assume that the ID column will always be the left most column.

Upvotes: 2

Views: 41913

Answers (2)

Stepan1010
Stepan1010

Reputation: 3136

I would say this is the most basic function of excel but since your assuming the artifical limitation that you can't decide how your columns are going to be ordered - then it requires you to use something like HLOOKUP (assuming you can at least determine your headers):

=IF(AND(HLOOKUP("Key",$A$1:$C$5,ROW(),FALSE)=10,HLOOKUP("VALUE",$A$1:$C$5,ROW(),FALSE)=20),HLOOKUP("ID",$A$1:$C$5,ROW(),FALSE),"")

HLOOKUP_EXAMPLE

Good Luck.

EDIT/ADDITION:

Use the multicat function from: http://www.mcgimpsey.com/excel/udfs/multicat.html

e.g. Sort your table to get rid of the spaces and then:

=multicat(C2:C5,",")

Or if sorting is too much work for you - you can use this modified version of the mcgimpsey function to get rid of blank cells:

  Public Function MultiCat( _
        ByRef rRng As Excel.Range, _
        Optional ByVal sDelim As String = "") _
             As String
     Dim rCell As Range
     For Each rCell In rRng
        If rCell.Value <> "" Then
         MultiCat = MultiCat & sDelim & rCell.Text
         End If
     Next rCell
     MultiCat = Mid(MultiCat, Len(sDelim) + 1)
  End Function

Upvotes: 0

nutsch
nutsch

Reputation: 5962

You can use the attached User Defined Function for that purpose. Call it from your worksheet as follows:

=concatPlusIfs(A1:A4,",",1,10,2,20)

where

  • A1:A4 is the ID list
  • "," is the separator
  • 1 is the offset between your id column and your key column (-1 for 1 column to the left)
  • 10 is the criteria for your Key
  • 2 is the offset between your id column and your Value column

  • 20 is the criteria for your Value

    Public Function concatPlusIfs(rng As Range, sep As String, lgCritOffset1 As Long, varCrit1 As Variant, lgCritOffset2 As Long, varCrit2 As Variant, Optional noDup As Boolean = False, Optional skipEmpty As Boolean = False) As String

Dim cl As Range, strTemp As String

If noDup Then 'remove duplicates, use collection to avoid them

Dim newCol As New Collection

On Error Resume Next

For Each cl In rng.Cells
    If skipEmpty = False Or Len(Trim(cl.Text)) > 0 Then
        If cl.Offset(, lgCritOffset1) = varCrit1 And cl.Offset(, lgCritOffset2) = varCrit2 Then newCol.Add cl.Text, cl.Text
    End If
Next

For i = 0 To newCol.Count
    strTemp = strTemp & newCol(i) & sep
Next

Else

For Each cl In rng.Cells
    If skipEmpty = False Or Len(Trim(cl.Text)) > 0 Then
        If cl.Offset(, lgCritOffset1) = varCrit1 And cl.Offset(, lgCritOffset2) = varCrit2 Then strTemp = strTemp & cl.Text & sep
    End If
Next

End If

concatPlusIfs = Left(strTemp, Len(strTemp) - Len(sep))

End Function

Upvotes: 1

Related Questions