Hi, Thanks for the help. I'll try to explain what I'd like to do, and I think the answer will require some combination of INDEX-MATCH, IF/IS STATEMENT, SEARCH, SMALL, maybe more, and an array (ctrl+shift+enter)
In Tab 1: (the one I'm using for look up values and want to bring back items from tab 2)
Down column 1, I have unique "program codes", like: A123456, A234567, BC123456, BC345678, etc. (note that all are unique, however not all are the same number of characters, i.e. when it starts with one letter it might be 7 characters, but if it starts with 2 letters, it is 8 characters)
In Tab 2: (Contains a column of values I want to bring back to tab 1)
Down column 1 contains the same unique "program codes" as in tab 1, (A123456, A234567, BC123456, BC345678, etc.)
Down column 2 contains "phase codes" like: C11, P11, C12, P12, PAYC11, PAYP11, PAYC12, PAYP12, etc. (note these are not unique and also vary in length of characters, but create unique combinations in relation to items in column 1 when concatenated)
Down column 3 contains the concatenated values, such as: A123456C11, A234567PAYC11, BC123456C12, BC345678PAYC12, etc.
Down column 4 contains the values I want to "bring back" to tab 1, values are text like "Y", "N", or some other text value.
The issue is that while A123456P11 might be "N", A123456C11 could be "Y". So in tab 1, I cannot use a plain vlookup of A123456 to the table/array on tab 2 because vlookup stops at the first match which happens to be A123456P11 and returns "N", when I also want to see the 2nd matching result (or 3rd, 4th, etc) which is next to A123456C11, or A123456PAYC11, A123456PAYC12 etc.
Trying to use some sort of combination of INDEX/MATCH, IF/IS, SEARCH, SMALL/LARGE, ROW formula to allow this, any ideas?
In Tab 1: (the one I'm using for look up values and want to bring back items from tab 2)
Down column 1, I have unique "program codes", like: A123456, A234567, BC123456, BC345678, etc. (note that all are unique, however not all are the same number of characters, i.e. when it starts with one letter it might be 7 characters, but if it starts with 2 letters, it is 8 characters)
In Tab 2: (Contains a column of values I want to bring back to tab 1)
Down column 1 contains the same unique "program codes" as in tab 1, (A123456, A234567, BC123456, BC345678, etc.)
Down column 2 contains "phase codes" like: C11, P11, C12, P12, PAYC11, PAYP11, PAYC12, PAYP12, etc. (note these are not unique and also vary in length of characters, but create unique combinations in relation to items in column 1 when concatenated)
Down column 3 contains the concatenated values, such as: A123456C11, A234567PAYC11, BC123456C12, BC345678PAYC12, etc.
Down column 4 contains the values I want to "bring back" to tab 1, values are text like "Y", "N", or some other text value.
The issue is that while A123456P11 might be "N", A123456C11 could be "Y". So in tab 1, I cannot use a plain vlookup of A123456 to the table/array on tab 2 because vlookup stops at the first match which happens to be A123456P11 and returns "N", when I also want to see the 2nd matching result (or 3rd, 4th, etc) which is next to A123456C11, or A123456PAYC11, A123456PAYC12 etc.
Trying to use some sort of combination of INDEX/MATCH, IF/IS, SEARCH, SMALL/LARGE, ROW formula to allow this, any ideas?
