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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][PLAN][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]UniqueId[/TD]
[TD="align: center"]Results[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]abcde[/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]zzzzz[/TD]
[TD]blue[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][RAW][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]UniqueID[/TD]
[TD="align: center"]Color[/TD]
[TD="align: center"]Size[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]abcde[/TD]
[TD]blue[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]abcde[/TD]
[TD]blue[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]abcde-AH1[/TD]
[TD]red[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]abcde-AH1[/TD]
[TD]red[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]abcde-AH2[/TD]
[TD]green[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]abcde-AH2[/TD]
[TD]orange[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]zzzzz-BH1
[/TD]
[TD]blue[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]zzzzz[/TD]
[TD]red[/TD]
[TD]S[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

UniqueID​

[/td][td]

Color​

[/td][td]

Size​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

abcde​

[/td][td]

blue​

[/td][td]

S​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

abcde​

[/td][td]

blue​

[/td][td]

M​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

abcde 123 ssss - AH1​

[/td][td]

red​

[/td][td]

L​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

abcde 123 ssss - AH1​

[/td][td]

red​

[/td][td]

L​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

abcde-AH2​

[/td][td]

green​

[/td][td]

L​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

abcde-AH2​

[/td][td]

orange​

[/td][td]

M​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

zzzzz 456 xxx - BH1​

[/td][td]

blue​

[/td][td]

M​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

zzzzz​

[/td][td]

red​

[/td][td]

S​

[/td][/tr]
[/table]

PLAN

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

UniqueId​

[/td][td]

Results​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

abcde​

[/td][td]

red​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

zzzzz​

[/td][td]

blue​

[/td][/tr]
[/table]

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

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

UniqueID​

[/TD]
[TD]

Color​

[/TD]
[TD]

Size​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

abcde​

[/TD]
[TD]

blue​

[/TD]
[TD]

S​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

abcde​

[/TD]
[TD]

blue​

[/TD]
[TD]

M​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

abcde 123 ssss - AH1​

[/TD]
[TD]

red​

[/TD]
[TD]

L
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

abcde 123 ssss - AH1​

[/TD]
[TD]

red
​

[/TD]
[TD]

S
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

abcde-AH2​

[/TD]
[TD]

green​

[/TD]
[TD]

L
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

abcde-AH2​

[/TD]
[TD]

orange​

[/TD]
[TD]

M
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

zzzzz 456 xxx - BH1​

[/TD]
[TD]

blue​

[/TD]
[TD]

S
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

zzzzz - BH1
​

[/TD]
[TD]

red
​

[/TD]
[TD]

L
​

[/TD]
[/TR]
</tbody>[/TABLE]



PLAN

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

UniqueId​

[/TD]
[TD]

Results​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

abcde​

[/TD]
[TD]

red​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

zzzzz
​

[/TD]
[TD]

red
​

[/TD]
[/TR]
</tbody>[/TABLE]



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.