Excel: Return multiple corresponding values for multiple lookup values

rox23

New Member
Joined
May 13, 2015
Messages
2
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
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

New Member
Joined
May 14, 2015
Messages
2
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.

Code:
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
 
Last edited:

rox23

New Member
Joined
May 13, 2015
Messages
2
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

New Member
Joined
Sep 24, 2015
Messages
7
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
ABC
1Record IDSketch #Sum of Mango Accepted
251101002732237
35110100333226
451102006588239
5511020065884216
651102006588438
7511020077884511
851102008631685

<tbody>
</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:
recordID
sketch#1mangoaccepted1sketch#2mangoaccepted2sketch#3mangoaccepted3

<tbody>
</tbody>

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
ABCDEFG
11recordId_keysketch#1mangoaccepted1sketch#2mangoaccepted2sketch#3mangoaccepted3
125110100273223#NUM!
13511010033322#NUM!
14511020065882388428843#NUM!
15511020065882388428843#NUM!
16511020065882388428843#NUM!
175110200778845#NUM!
185110200863168#NUM!

<tbody>
</tbody>
Sheet2

Array Formulas
CellFormula
D14{=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)))}
E14{=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)))}
D15{=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)))}
E15{=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)))}
D16{=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)))}
E16{=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)))}
B12{=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)))}
C12{=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)))}
B13{=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)))}
C13{=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)))}
B14{=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)))}
C14{=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)))}
B15{=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)))}
C15{=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)))}
B16{=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)))}
C16{=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)))}
B17{=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)))}
C17{=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)))}
B18{=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)))}
C18{=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)))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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
ABCDEFG
20recordId_keysketch#1mangoaccepted1sketch#2mangoaccepted2sketch#3mangoaccepted3
2151101002732237#REF!
22#NUM!
23

<tbody>
</tbody>
Sheet2

Array Formulas
CellFormula
A21{=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))}
B21{=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))}
C21{=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))}
D21{=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))}
A22{=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))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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

Watch MrExcel Video

Forum statistics

Threads
1,132,672
Messages
5,654,669
Members
418,147
Latest member
dorkas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top