Looking up a value in data source, matching on another criteria and outputting a value from a third column

[stovvy]

I have the following query and cannot for the life of me get any kind of nested formula to work. What I require is it to look at cell A1 in Sheet 2 and in Sheet 1 look for that cell in Column A (which appears more than once), but then look at Column B for a zero and then take the value from Column C that corresponds and place it it Sheet 2 in Cell B1. I would then need it to find the same value, but this time 1 in Column B and then place Column C's figure in Sheet 2 cell C1. This needs to be repeated up to 6 times and for multiple lookups.

Sheet 1 Sheet 2
A B C A B C D E F G
1 Dave 0 15 1 Dave 15 13 12 9 7 5
2 Dave 2 12 2 Fred 3 6 8 13 15 17
3 Dave 5 5
4 Dave 1 13
5 Dave 3 9
6 Dave 4 7
7 Fred 5 17
8 Fred 3 13
9 Fred 2 8
10 Fred 4 15
11 Fred 1 6
12 Fred 0 3

Any help would be gratefully received

Thanks

 

[stovvy]

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1							0	1	2	3	4	5
2	Dave	0	15			Dave	15	13	12	9	7	5
3	Dave	2	12			Fred	3	6	8	13	15	17
4	Dave	5	5
5	Dave	1	13
6	Dave	3	9
7	Dave	4	7
8	Fred	5	17
9	Fred	3	13
10	Fred	2	8
11	Fred	4	15
12	Fred	1	6
13	Fred	0	3
Lists

Cell Formulas
Range	Formula
G2	{=IFERROR(INDEX($C$2:$C$13,MATCH(1,($A$2:$A$13=$F2)*($B$2:$B$13=G$1),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Also, you cannot show pictures that are on your computer. They would need to be uploaded to a share site.

 

[stovvy]

Brilliant!!! many, many thanks

 

[mumps]

VBA alternative: You would need to insert a header row in Sheet1 so that the data starts in row 2. The macro assumes you are using 2 sheets with the result going to Sheet2.

Sub stovvy()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, rName As Range
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("B2:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:D" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For Each rName In desWS.Range("A1", desWS.Range("A" & desWS.Rows.Count).End(xlUp))
        With srcWS.Cells(1).CurrentRegion
            .AutoFilter 1, rName
            srcWS.Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy
            rName.Offset(0, 1).PasteSpecial Transpose:=True
            .AutoFilter
        End With
    Next rName
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Brilliant!!! many, many thanks

You're welcome & thanks for the feedback