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]

I didn't notice the new requirement "-DD1"

Try this new version

Array formula in B2 copied down
=INDEX(RAW!B$2:B$9,MATCH(1,IF(LEFT(RAW!A$2:A$9,5)=A2,IF(ISNUMBER(MATCH(RIGHT(RAW!A$2:A$9,3),{"AH1";"BH1";"DH1"},0)),IF(RAW!C$2:C$9="L",1))),0))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[kparadise]

Could you please adjust the formula to SEARCH the beginning of the cell and the end of the cell, instead of looking for a number of characters?

 

[Marcelo Branco]

Post 5

But it will always START with the five letter uniqueID on the [PLAN] tab; and END with values after the dash.

As you said that always the first 5 characters correspond to the uniqueiID and the last three to AH1, BH1 etc, we have to use LEFT(text, 5) and RIGHT(text, 3)
Why and how to do it differently?

M.

 

[kparadise]

I am anticipating changes.

Can we a search; if the 5 letter unique ids appear in the raw data & if the 3 letter appears in the same raw data?

I know the 5 letter unique ID will be added; or possibly show in the middle of the cell. I also know eventually the "-AH1" will be "AH0001"... So I am just trying to make as dynamic as possible now that I have new information. I am sorry this changed...but can we do a SEARCH instead?

 

[Marcelo Branco]

You can replace LEFT(RAW!A$2:A$9,5)=A2 by ISNUMBER(SEARCH(A2,RAW!A$2:A$9))

As for the suffixes is more complicated, because the formula needs to know if you are looking for AH1, AH01, AH001 etc.
Maybe putting the suffix in a criteria cell and using the reference to this cell in the formula. I can only be sure if it works by testing in some examples ...

M.

 

[kparadise]

Can I just do another ISNUMBER(SEARCH({"*AH1", "*AH001", "*DD1*"}, RAW!A$2:A$9)) ??

 

[Marcelo Branco]

hmm.. see if this works

B2 copied down
=INDEX(RAW!B$2:B$9,MATCH(1,IF(ISNUMBER(SEARCH(A2,RAW!A$2:A$9)),IF(ISNUMBER(SEARCH("AH*1",RAW!A$2:A$9))+ISNUMBER(SEARCH("BH*1",RAW!A$2:A$9))+ISNUMBER(SEARCH("DH*1",RAW!A$2:A$9)),IF(RAW!C$2:C$9="L",1))),0))
Ctrl+Shift+Enter

M.

 

[kparadise]

This is working great!

It has been a long road; and I appreciate all the help and twists and turns! Thank you very much!

 

[Marcelo Branco]

New version - I think it's much better


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

A​

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

B​

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

C​

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

D​

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

E​

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

1​

[/td][td]

UniqueId​

[/td][td]

Results​

[/td][td][/td][td]

Suffixes​

[/td][td]

Size​

[/td][/tr]

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

2​

[/td][td]

abcde​

[/td][td]

red​

[/td][td][/td][td]

AH*1​

[/td][td]

L​

[/td][/tr]

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

3​

[/td][td]

zzzzz​

[/td][td]

red​

[/td][td][/td][td]

BH*1​

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

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

4​

[/td][td][/td][td][/td][td][/td][td]

DH*1​

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

Criteria in columns D:E (observe the * in column D)

Formula in B2 copied down
=INDEX(RAW!B$2:B$9,MATCH(1,IF(ISNUMBER(SEARCH(A2,RAW!A$2:A$9)),IF(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(D$2:D$4),RAW!A$2:A$9)),ROW(D$2:D$4)^0),IF(RAW!C$2:C$9=E$2,1))),0))
Ctrl+Shift+Enter

M.