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.
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.
I cannot find how to attach my excel sheet so I took some snapshots.
Sheet 1 Columns AR, AP, & AQ will contain the formulas.
Sheet 1 Column J contains the lookup value. Will only be one value for each number in this column.
Sheet 2 Column A Contains the return value that I want to write to the first page for US Lifecycles.
Sheet 2 Column B Contains the return value that I want to write to the first page for Canada Lifecycles.
Sheet 2 Column C Contains the return value that I want to write to the first page for Puerto Rico Lifecycles.
Sheet 2 Column G contains my matching values. There can be up to 3 matching values in this entire column; one per row - not in any particular order. *This is what my index match is missing... it is stopping at the first match and returning blanks for the other lifecycles when it may not be a blank.*
Sheet 2 Column K -I added this column to try to add a 2nd condition for each lookup.
My normal index match formula that is not working correctly to continue searching through my Column G for matches that are not blanks, unless there truly is a blank:
=IFERROR(INDEX('My 2nd Sheet'!$A$1:$A$1587,MATCH(J4,'My 2nd Sheet'!$G$1:$G$1587,0)),"")
Can anyone please help me?
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.
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.
I cannot find how to attach my excel sheet so I took some snapshots.
Sheet 1 Columns AR, AP, & AQ will contain the formulas.
Sheet 1 Column J contains the lookup value. Will only be one value for each number in this column.
Sheet 2 Column A Contains the return value that I want to write to the first page for US Lifecycles.
Sheet 2 Column B Contains the return value that I want to write to the first page for Canada Lifecycles.
Sheet 2 Column C Contains the return value that I want to write to the first page for Puerto Rico Lifecycles.
Sheet 2 Column G contains my matching values. There can be up to 3 matching values in this entire column; one per row - not in any particular order. *This is what my index match is missing... it is stopping at the first match and returning blanks for the other lifecycles when it may not be a blank.*
Sheet 2 Column K -I added this column to try to add a 2nd condition for each lookup.
My normal index match formula that is not working correctly to continue searching through my Column G for matches that are not blanks, unless there truly is a blank:
=IFERROR(INDEX('My 2nd Sheet'!$A$1:$A$1587,MATCH(J4,'My 2nd Sheet'!$G$1:$G$1587,0)),"")
Can anyone please help me?
