Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

find second match if prior cell has same lookup value

Posted on 2013-12-20
9
428 Views
Last Modified: 2013-12-30
What would be the best way to lookup values, if the next row contained same lookup value as the previous,so the first lookup (A5) pulls the first match and the second lookup (A6) with same value pulls the second match.
0
Comment
Question by:IO_Dork
  • 5
  • 4
9 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39733221
Try array entering a formula like:
=IFERROR(INDEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!A$1:A$10=A5,ROW(Sheet1!B$1:B$10)-ROW(Sheet1!B$1)+1,""),COUNTIF(A$4:A4,A5)+1)),"")
This formula will return the first, second, third result from Sheet1 column B when Sheet1 column A contains the same value as cell A5. The formula returns an empty string (looks like a blank) when there are no more matches.

The above formula must be array-entered. To do so:
1.  Select the cell
2.  Click in the formula bar
3.  Hold the Control and Shift keys down
4.  Hit the Enter key, then release all three keys
Excel should respond by adding curly braces { } surrounding the formula. If not (or if you see a blank when there should be a value returned), then repeat steps 1 through 4
MultipleMatchesQ28323513.xlsx
0
 

Author Comment

by:IO_Dork
ID: 39740568
this is a long and tough one...maybe you could help me plug in the correct reference based on my current vlookup formula:

=VLOOKUP(A2,Prev!A:N,14,FALSE)
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39740713
Could you please post a sample workbook? My concern is where the formula goes and how to determine whether you want the first, second, third, etc. match.

Here is an array formula that assumes you keep repeating the value in A2 in column A for as many matches as desired. You can see it in action in the "Copy Down" worksheet.
=IFERROR(INDEX(Prev!N:N,SMALL(IF(Prev!A$1:A1000=A2,ROW(Prev!A$1:A$1000),""),COUNTIF(A$2:A2,A2))),"")

Here is another array formula that assumes you want the first match in column B, second in column C, etc. You can see it in action in the "Copy Across" worksheet.
=IFERROR(INDEX(Prev!$N:$N,SMALL(IF(Prev!$A$1:$A1000=$A2,ROW(Prev!$A$1:$A$1000),""),COLUMNS($B2:B2))),"")

Both formulas must be array-entered using the instructions previously furnished.
MultipleLookupsQ28323513.xlsx
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:IO_Dork
ID: 39740745
I'll cleanup and simplify the workbook and pust it here later.  I have to leave now and travel for a few hours.
0
 

Author Comment

by:IO_Dork
ID: 39742308
columns H and N (highligted in yellow) on sheet "Curr" are the ones with the lookup formulas that need to be replaced with your formula.
lookup-sample.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 39742404
Array formulas are sensitive to the number of rows being searched, and can cause an annoying recalc delay if you search entire columns (over a million rows). I had previously limited the array formula to 1000 rows of data, but suspect you may need more than that. In the workbook you posted, I expanded that to 10000 rows, and trust that you can increase it further if necessary.

Since most of the lookups were for the first value only, I decided to test whether the lookup value (A2) was the same as the previous row. If not, a regular VLOOKUP using entire columns would suffice. In so doing, you avoid the need for performing the array-entered part of the formula in most cases. Note that this construction assumes that column A has been sorted so duplicate values are in adjacent cells.

Resulting formula for column H:
=IFERROR(IF($A2<>$A1,VLOOKUP($A2,Prev!$A:$N,8,FALSE),INDEX(Prev!H:H,SMALL(IF(Prev!$A$1:$A10000=$A2,ROW(Prev!$A$1:$A$10000),""),COUNTIF($A$2:$A2,$A2)))),"")

Resulting formula for column N:
=IFERROR(IF($A2<>$A1,VLOOKUP($A2,Prev!$A:$N,14,FALSE),INDEX(Prev!N:N,SMALL(IF(Prev!$A$1:$A10000=$A2,ROW(Prev!$A$1:$A$10000),""),COUNTIF($A$2:$A2,$A2)))),"")

These formulas return an empty string (looks like a blank) if there was no match for A2. If you prefer #NA! error value, then shorten the formulas to:
=IF($A2<>$A1,VLOOKUP($A2,Prev!$A:$N,8,FALSE),INDEX(Prev!H:H,SMALL(IF(Prev!$A$1:$A10000=$A2,ROW(Prev!$A$1:$A$10000),""),COUNTIF($A$2:$A2,$A2))))

=IF($A2<>$A1,VLOOKUP($A2,Prev!$A:$N,14,FALSE),INDEX(Prev!N:N,SMALL(IF(Prev!$A$1:$A10000=$A2,ROW(Prev!$A$1:$A$10000),""),COUNTIF($A$2:$A2,$A2))))

All of the formulas in this Comment need to be array-entered.

The sample workbook shows the first pair of formulas in row 3 and following. It shows the second pair of formulas in row 2.

Brad
lookup-sampleQ28323513.xlsx
0
 

Author Comment

by:IO_Dork
ID: 39742434
so do i have to ctrl+shift enter these arrays one at a time or can I select all of column a and do the ctrl+shift all at once to make them arrays?
0
 
LVL 81

Expert Comment

by:byundt
ID: 39742644
Do not select an entire column and array-enter the formula into all the cells. If you do, you will get the answer for the first row repeated in every cell in that column.

Instead CTRL + Shift + Enter the first formula. Then select that cell and double-click the little square at bottom right corner of the selection marquee. Excel will then copy down the array formula into all the data cells in that column.
0
 

Author Closing Comment

by:IO_Dork
ID: 39746708
thanks, great solution.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question