Index/Match If

[kparadise]

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".

[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>

 

[Marcelo Branco]

Try

=INDEX(RAW!B:B,IFERROR(MATCH(A2&"-AH1",RAW!A:A,0),MATCH(A2&"-BH1",RAW!A:A,0)))

M.

 

[kparadise]

That is not working for me. How do I incorporate to Search the end of the cell for "-AH" or "-BH1"; and search the front of the cell for the UniqueID? Isn't this formula looking for a cell with "AH1" only?

 

[Marcelo Branco]

If it's not working it seems that in RAW sheet there are spaces either between the ID and the dash or between dash and AH1 (BH1).

See if this works
=INDEX(RAW!B:B,IFERROR(MATCH(A2&"*-*AH1",RAW!A:A,0),MATCH(A2&"*-*BH1",RAW!A:A,0)))

M.

 

[kparadise]

Sometimes the IDs will not look exactly like that.....there might be more characters in between the "abcde" and the "*-AH1"...that is why I am asking is there a way to search the cell that starts with the 5 letter ID, and ends in "-AH1" OR "-BH1". There might be other characters in there... i.e. "abcde-123-ssss-AH1". But it will always START with the five letter uniqueID on the [PLAN] tab; and END with values after the dash.

 

[Marcelo Branco]

The formula in post 4 worked for me using the scenario below

RAW

	A​	B​	C​
1​	UniqueID​	Color​	Size​
2​	abcde​	blue​	S​
3​	abcde​	blue​	M​
4​	abcde 123 ssss - AH1​	red​	L​
5​	abcde 123 ssss - AH1​	red​	L​
6​	abcde-AH2​	green​	L​
7​	abcde-AH2​	orange​	M​
8​	zzzzz 456 xxx - BH1​	blue​	M​
9​	zzzzz​	red​	S​

PLAN

	A​	B​
1​	UniqueId​	Results​
2​	abcde​	red​
3​	zzzzz​	blue​

Formula in B2 (see post 4)
=INDEX(RAW!B:B,IFERROR(MATCH(A2&"*-*AH1",RAW!A:A,0),MATCH(A2&"*-*BH1",RAW!A:A,0)))

M.

 

[kparadise]

Ok, that seems to work very well!

What if I wanted to add one more criteria. Where would I put it? AND size equals "L".

Also, if I wanted to add another OR in there; where would I out it? OR "-DD1".

 

[Marcelo Branco]

Ok, that seems to work very well!

What if I wanted to add one more criteria. Where would I put it?

What criteria? We need more information - data and expected result.

M.

 

[kparadise]

RAW

	A​	B​	C ​
1​	UniqueID​	Color​	Size​
2​	abcde​	blue​	S​
3​	abcde​	blue​	M​
4​	abcde 123 ssss - AH1​	red​	L ​
5​	abcde 123 ssss - AH1​	red ​	S ​
6​	abcde-AH2​	green​	L ​
7​	abcde-AH2​	orange​	M ​
8​	zzzzz 456 xxx - BH1​	blue​	S ​
9​	zzzzz - BH1 ​	red ​	L ​

<tbody>
</tbody>

PLAN

	A​	B​
1​	UniqueId​	Results​
2​	abcde​	red​
3​	zzzzz ​	red ​

<tbody>
</tbody>

So, the Size in the [RAW] tab for the corresponding record needs to be "L".

I also want to add another option for the ending of the UniqueID to be a "-DD1".

 

[Marcelo Branco]

Try this array formula in B2 copied down

=INDEX(RAW!B$2:B$9,MATCH(1,IF(LEFT(RAW!A$2:A$9,5)=A2,IF((RIGHT(RAW!A$2:A$9,3)="AH1")+(RIGHT(RAW!A$2:A$9,3)="BH1"),IF(RAW!C$2:C$9="L",1))),0))
confirmed with Ctrl+Shift+Enter, not just Enter

M.