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]

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

	A​	B​	C​	D​	E​
1​	UniqueId​	Results​		Suffixes​	Size​
2​	abcde​	red​		AH*1​	L​
3​	zzzzz​	red​		BH*1​
4​				DH*1​

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.