Excel: Return multiple corresponding values for multiple lookup values

[rox23]

Hello,

I'm sorry if this question has been asked already but I've been looking for an answer for a while to no avail.

I would like to know how to lookup multiple values using multiple criteria. Let us say that in column A are last names, in column B are first names, and in column C are phone numbers. In this list, a person's name may occur many times since one person may have many phone numbers.

A B C
Smith Ben 12345
Smith Ben 67890
Smith Sarah 09876
Andrews Maria 54321
Andrews Maria 11223
Johns Teresa 99876

Lookup: Ben Smith's numbers?

I know how to use index match to find one value using multiple criteria, but can't find multiple values using multiple criteria.

I am using MS Excel 2010.

Thanks in advance for your help!!

R

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

The question as you asked it can be met by using the AutoFilter feature to select the names.

If that is not of any use to you, you will need to say how you want to use the results. For instance, are you going to use the numbers in VBA or do you want a concatenated string to place in a workbook cell or something else?

Thanks,

 

[accydm]

Something like this would work if you wanted to use VBA - it's only a quick and dirty thing I've knocked up but it does work.

It pastes the results into columns F:H.

Sub namefinder()

Dim fname, sname, tname As String
Dim Qty, cnt As Integer

cnt = 0
tot = 0
sname = InputBox("Please Enter the Surname you wish to search for", "Surname")
fname = InputBox("Please Enter the First name you wish to search for", "First Name")
tname = sname & fname
Qty = WorksheetFunction.CountIfs(ActiveSheet.Range("A:A"), sname, ActiveSheet.Range("B:B"), fname)

ActiveSheet.Range("F:H").ClearContents

Do Until tot = Qty
If ActiveSheet.Range("A1").Offset(cnt, 0).Value & ActiveSheet.Range("B1").Offset(cnt, 0).Value = tname Then
    no = ActiveSheet.Range("C1").Offset(cnt, 0).Value
    cnt = cnt + 1
    ActiveSheet.Range("F1").Offset(tot, 0).Value = sname
    ActiveSheet.Range("G1").Offset(tot, 0).Value = fname
    ActiveSheet.Range("H1").Offset(tot, 0).Value = no
    tot = tot + 1
Else
cnt = cnt + 1
End If
Loop
End Sub

But like RickXL said - it really does depend upon what you want to use these things for, if you tell us that we might be able to give a more relevant solution

Hope this helps

 

[rox23]

Hi all,

Thanks for the responses. Indeed, I'm not using VBA, but I'll definitely keep the code handy! I'm looking to use just formulae to keep things relatively simple for myself at this point in time, but VBA could come into it in the future.

I've managed to come up with something like the below (in so many words), so maybe this could come in handy for others to use (I know it's likely a bit clunky, but I'm still pretty proud of myself for managing something like this!)

index(result range, small(if(isnumber((search(surname ref, surname range))*(search(first name ref, first name range))),row(first name range)-min(row(first name range))+1,""),column(corner header)))

Thanks for your help!!

R

 

[mangotango]

Hi All, I've run into a similar challenge trying to return multiple corresponding values from multiple lookup values (with duplicates). Here's a dataset:
Excel 2010

	A	B	C
Record ID	Sketch #	Sum of Mango Accepted

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]511010027[/TD]
[TD="align: right"]3223[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]511010033[/TD]
[TD="align: right"]322[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]511020065[/TD]
[TD="align: right"]8823[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]511020065[/TD]
[TD="align: right"]8842[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]511020065[/TD]
[TD="align: right"]8843[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]511020077[/TD]
[TD="align: right"]8845[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]511020086[/TD]
[TD="align: right"]3168[/TD]
[TD="align: right"]5[/TD]

</tbody>

Sheet2​

Effectively, I want to lookup the values in Column A and return the corresponding values in Columns B and C horizontally following this format:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]recordID
[/TD]
[TD]sketch#1[/TD]
[TD]mangoaccepted1[/TD]
[TD]sketch#2[/TD]
[TD]mangoaccepted2[/TD]
[TD]sketch#3[/TD]
[TD]mangoaccepted3[/TD]
[/TR]
</tbody>[/TABLE]

I've tried the array: =INDEX($B$2:$B$8, SMALL(IF($A2=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(A1))) which will only return the values from Column B.
Excel 2010

	A	B	C	D	E	F	G
recordId_key	sketch#1	mangoaccepted1	sketch#2	mangoaccepted2	sketch#3	mangoaccepted3

<tbody>
[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]511010027[/TD]
[TD="align: right"]3223[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]511010033[/TD]
[TD="align: right"]322[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]511020065[/TD]
[TD="align: right"]8823[/TD]
[TD="align: right"]8842[/TD]
[TD="align: right"]8843[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]511020065[/TD]
[TD="align: right"]8823[/TD]
[TD="align: right"]8842[/TD]
[TD="align: right"]8843[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]511020065[/TD]
[TD="align: right"]8823[/TD]
[TD="align: right"]8842[/TD]
[TD="align: right"]8843[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]511020077[/TD]
[TD="align: right"]8845[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]511020086[/TD]
[TD="align: right"]3168[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D14[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A4=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(C3)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E14[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A4=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(D3)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D15[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A5=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(C4)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E15[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A5=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(D4)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D16[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A6=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(C5)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E16[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A6=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(D5)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B12[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A2=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(A1)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C12[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A2=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(B1)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A3=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(A2)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C13[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A3=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(B2)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B14[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A4=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(A3)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C14[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A4=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(B3)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B15[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A5=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(A4)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C15[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A5=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(B4)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B16[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A6=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(A5)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C16[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A6=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(B5)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B17[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A7=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(A6)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C17[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A7=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(B6)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B18[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A8=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(A7)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C18[/TH]
[TD="align: left"]{=INDEX($B$2:$B$8, SMALL(IF($A8=$A$2:$B$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), COLUMN(B7)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I've tried the array: =INDEX($A$2:$C$8, SMALL(IF($A2=$A$2:$A$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), ROW(A1)),COLUMN(A1)) which will return the values from both Columns B and C but will not allow me to copy+paste (or drag) the formula down Column A.
Excel 2010

	A	B	C	D	E	F	G
recordId_key	sketch#1	mangoaccepted1	sketch#2	mangoaccepted2	sketch#3	mangoaccepted3

<tbody>
[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]511010027[/TD]
[TD="align: right"]3223[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]#REF![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A21[/TH]
[TD="align: left"]{=INDEX($A$2:$C$8, SMALL(IF($A2=$A$2:$A$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), ROW(A1)),COLUMN(A1))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B21[/TH]
[TD="align: left"]{=INDEX($A$2:$C$8, SMALL(IF($A2=$A$2:$A$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), ROW(B1)),COLUMN(B1))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C21[/TH]
[TD="align: left"]{=INDEX($A$2:$C$8, SMALL(IF($A2=$A$2:$A$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), ROW(C1)),COLUMN(C1))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D21[/TH]
[TD="align: left"]{=INDEX($A$2:$C$8, SMALL(IF($A2=$A$2:$A$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), ROW(D1)),COLUMN(D1))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A22[/TH]
[TD="align: left"]{=INDEX($A$2:$C$8, SMALL(IF($A3=$A$2:$A$8, ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1, ""), ROW(A2)),COLUMN(A2))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

I'm sure I'm missing just one piece of the puzzle but any help will be greatly appreciated...thanks!