0

I was thinking of a double VBA VLOOKUP or a double conditional If for that 2 cells and return another cell from the specific row back on Sheet1.

Basically a double VBA VLOOKUP or a double conditional IF when the strings were found I'd be going with the VLOOKUP in VBA , sort of look for A2 and D2 from Sheet 1 in Sheet 2 Range A1:A999 and D2:D999 and if the results match output C cell from the same row back on C2 in Sheet 1

But not sure how to proceed further. Any advice would be more than appreciated. Thanks!

Output on the yellow Cell

Sheet2 where the VBA VLOOKUP will take place

Improve this question
4

2 Answers 2

Reset to default
0

Doable entirely without VBA:

=INDEX(Sheet2!$C$1:$C$999, AGGREGATE(15, 6, ROW(Sheet2!$A$1:$A$999)/(--(Sheet2!$A$1:$A$999=$A1)*--(Sheet2!$B$1:$B$999=$B1)), 1), 1)

The obvious bit is the Index - replace the AGGREGATE with a MATCH, and you probably already recognise it:

=INDEX(Sheet2!$C$1:$C$999, MATCH(???), 1)

However, instead of using MATCH, we are using the AGGREGATE function

AGGREGATE(15, 6, ROW(Sheet2!$A$1:$A$999)/(--(Sheet2!$A$1:$A$999=$A1)*--(Sheet2!$B$1:$B$999=$B1)), 1)
  1. The first argument (15) just tells us to look for the kth SMALLest value in a list. This is similar to using the SMALL function
  2. The second argument (6) tells us to discard any error values in the list.
  3. The third argument is the list to look at - we'll break this down later
  4. The final argument (1) tells us to get the 1st smallest value from the list - effectively MIN, but MIN doesn't play nicely with calculated lists

So, what is our List then. Let's look at the formula:

ROW(Sheet2!$A$1:$A$999)/(--(Sheet2!$A$1:$A$999=$A1)*--(Sheet2!$B$1:$B$999=$B1))

There are two important parts here - first, ROW(Sheet2!$A$1:$A$999) will output the row number that we want, so that we can use it in the INDEX. Secondly, we divide it by a value which will be 1 when Column A matches (--(Sheet2!$A$1:$A$999=$A1)) and Column B matches (--(Sheet2!$B$1:$B$999=$B1)), but 0 when either of them doesn't. And, anything divided by 0 gives a #DIV0! error - which means it is discarded from our list!

So, the output of the AGGREGATE is the smallest Row number where the values match. If there is no such row, then you will get a #NUM! error instead.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

So what about the second condition regarding D2 cell from Sheet1 to be found on Sheet2?
@Gordon ... Have you tried just changing the columns it looks at? INDEX(Sheet2!$D$1:$D$999 will pull the gender, while --(Sheet2!$D$1:$D$999=$D1) would search for it. (Do note that you haven't labelled which of your pictures is Sheet1 or Sheet2 in the question)
The picture with a yellow cell is Sheet 1 and the 2nd picture is Sheet2. I am trying to do this by using VBA. Thanks !
0

The following vba macro might be of help:

Sub lookupMatch()
    Dim TotalCols As Long
    Dim rng As Range
    Dim i As Long

    'Copy matched up values from sheet2 to sheet1
    Sheets("Sheet2").Select
    TotalCols = ActiveSheet.UsedRange.Columns.Count

    'Changed Cols to Columns on the above line.

    Range("C1:C" & TotalCols).Copy Destination:=Sheets("Sheet1").Range("C2")

    'destination sheet
    Sheets("Sheet2").Select

    For i = 1 To TotalCols
        'Search for the value on sheet2
        Set rng = Sheets("Sheet2").UsedRange.Find(Cells(i, 3).Value)
        'If found put its value on destination sheet
        If Not rng Is Nothing Then
            Cells(i, 2).Value = rng.Value
        End If
    Next
End Sub
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.