Lookup help - Index Match does stops at the first or another way to get the results...

[girltoni]

Hello,
I am open to how I solve this. The issue is I have one sheet in a workbook that contains the lookup value to another sheet.

In the other sheet, I have added a column to try to resolve - but to no avail.

The other sheet has 3 Columns - Column A - US Lifecycle ,Column B - Canada Lifecycle, and Column C - Puerto Rico Lifecycle. These contain the numbers I want to return. on my first sheet. To match these, I am matching the lookup from the first sheet to another column G on this second sheet. *IN a lot of instances, G will have the same number on different rows (up to 3 times). For these instances, I need to capture the lookup value and return the correct number of the lifecycle columns to my first sheet. The issue seems to be that excel sees the first row in the column, and then stops at the row; even though there may or may not be additional rows that hold another match column G with my number on the first sheet and then returns whichever value in A, B, C that I looked up first. So sometimes, I am getting blanks because the formula stopped at the first match.

In the added column K - I have US, PR, CA all in the same column as values. I thought maybe if I cannot have it loop through every cell, I could throw another catch for each lifecycle column.

[hrlngrv]

Your workbook attachment isn't useful because it only contains screen shot images of another workbook which you're not providing.

I'm going to guess that all you need in the 1st worksheet is

AP4:

Please Login or Register  to view this content.

Fill AP4 right into AQ4:AR4, then select AP4:AR4 and fill down as far as needed.

[girltoni]

Sorry, I am not needing a sum or total; just the value. But I realize I should have included a workbook so you can understand better. There are values in the 2nd sheet that I am needing to write over to the first sheet. I am matching a # from the first sheet to the 2nd sheet. And sometimes up to (3) rows will have the same # that it matches. Therefore, my index match is not working correctly as it stops when it sees the first match and returns a blank. However, there may or may not be a value for it. I need it to at least loop through the entire set of columns that it is matching before it returns the blank to check the other rows as well. Hope this helps! Also, I recreated my sheet to take out confidential info so that it may be more helpful.

The values that are being brought over will either be 1, 2, 3, 4, 5, 6, 7 or blank if the 2nd sheet columns A B or C do not have values.

[hrlngrv]

Sorry. I hadn't noticed there could be multiple instances of the same code in col J in the 1st worksheet.

Code 80324133 appears once in the 1st worksheet but twice in the 2nd worksheet. Should only the topmost match from the 2nd worksheet be reflected for that code in the 1st worksheet? Code 80342966 appears twice in the 1st worksheet but just once in the 2nd worksheet. Should both rows in the 1st worksheet reflect the same row in the 2nd worksheet, or should the 2nd row with that code in the 1st worksheet remain blank?

There also appear to be data problems in the 2nd worksheet's columns A to C. There are 14 rows in col A containing 0, and 13 rows in col B containing 0. Should 0 ever be returned to the 1st worksheet?

If the 1st worksheet should never show entries in more than one of columns AP to AR in any single row, then I'd assume the 1st time a code appears in the 1st worksheet it should reference the topmost matching row in the 2nd worksheet; the 2nd time a code appears in the 1st worksheet it should reference the next topmost matching row in the 2nd worksheets; and so on. If that's correct, I believe you'd still need multiple cells per result.

Add these formulas in the 1st worksheet.

AT4: 1

AT5: =1+COUNTIF(J$4:J4,J5)

AU4: =MATCH(J4,'My 2nd Sheet'!$G$2:$G$1587,0)

AV4: =IF(AT4>1,AU4+MATCH($J4,INDEX('My 2nd Sheet'!$G$2:$G$1587,AU4+1):'My 2nd Sheet'!$G$1587,0),#N/A)

AW4: =IF(AT4>2,AV4+MATCH($J4,INDEX('My 2nd Sheet'!$G$2:$G$1587,AV4+1):'My 2nd Sheet'!$G$1587,0),#N/A)

Select AU4:AW4 and fill down into AU5:AW5. Then select AT5:AW5 and fill down as far as needed, which seems to be into AT6:AW945.

Then you can use array formulas to for each row in columns AP to AR. I entered them here in columns AX to AZ. Select AX4:AZ4, type the formula

=IFERROR(INDEX('My 2nd Sheet'!$A$2:$C$1587,INDEX(AU4:AW4,AT4),0),"")

hold down [Ctrl] and [Shift] keys and then press [Enter]. You can compare the contents of columns AX to AZ against AP to AR in rows in which col AT values are greater than 1, which have yellow background color.