Hello,
I am looking for a formula. I have one tab with raw data, multiple records, multiple duplicates, etc (tab named [raw]). I have another tab [Plan] where I need to insert my formulas. In my reference cell A1; I will enter a unique ID number. I want to use that value I entered in A1 and look it up in the [raw] tab; and return a value in column c of the [raw] tab.
This is a simple Index Match formula, which is not my issue. The additional piece of this formula is, I need to find the corresponding cell in the [raw] tab that STARTS with my unique ID, and ends with "-AH1" or "-BH1".
<tbody>
</tbody>
<tbody>
</tbody>
I am looking for a formula. I have one tab with raw data, multiple records, multiple duplicates, etc (tab named [raw]). I have another tab [Plan] where I need to insert my formulas. In my reference cell A1; I will enter a unique ID number. I want to use that value I entered in A1 and look it up in the [raw] tab; and return a value in column c of the [raw] tab.
This is a simple Index Match formula, which is not my issue. The additional piece of this formula is, I need to find the corresponding cell in the [raw] tab that STARTS with my unique ID, and ends with "-AH1" or "-BH1".
[PLAN] | A | B |
1 | UniqueId | Results |
2 | abcde | red |
3 | zzzzz | blue |
<tbody>
</tbody>
[RAW] | A | B | C |
1 | UniqueID | Color | Size |
2 | abcde | blue | S |
3 | abcde | blue | M |
4 | abcde-AH1 | red | L |
5 | abcde-AH1 | red | L |
6 | abcde-AH2 | green | L |
7 | abcde-AH2 | orange | M |
8 | zzzzz-BH1 | blue | M |
9 | zzzzz | red | S |
<tbody>
</tbody>
Last edited: