Need help with nested if statements using match

[tonyg88]

Hi All,

Struggling with an excel problem. I have one list (list 1) with IDs and a corresponding value (letter).

Separately, i have a list of IDs (list 2) that I want to put the latest value for (see output columns for what i mean). I want to check first if a 2 -C exists, if not, check if a 2-B exists, whichever is the latest, display that value for the '2' ID.

Here's how i tackled it in excel:

1. created a column for concatenated values for list 1
2. Wrote nested if statement in output column of list 2
3. nested if statement:
   1. =IF(MATCH(CONCATENATE(D1,", ","C"),C;C,0),"C",
   2. IF(MATCH(CONCATENATE(D1,", ","B"),C;C,0),"B",
      IF(MATCH(CONCATENATE(D1,", ","A"),C:C,0),"A",0)))
      
      
      Error: the formula returns a #N/A value

Note - I simplified the below example for readability, the real problem has more complicated IDs and the letter column has more characters.

List 1:

ID <tbody> </tbody>	Letter	concatenate (Column C) <tbody> </tbody>
1	A	1, A
1	B	1, B
1	C	1, C
2	A	2, A
2	A	2, A
2	B	2, B
2	B	2, B
2	B	2, B

<tbody>
</tbody>

List 2:

ID (column D)	Output
1	C
1	C
2	B
2	B

<tbody>
</tbody>

 

[jtakw]

Hi,

Does this do what you need, if it does, your Column C is Not needed:

Book1
	A	B	C	D	E
1	List 1:			List 2:
2
3	ID	Letter	concatenate (Column C)	ID (column D)	Output
4	1	A	1, A	1	C
5	1	B	1, B	1	C
6	1	C	1, C	2	B
7	2	A	2, A	2	B
8	2	A	2, A
9	11	B	2, B
10	2	B	2, B
11	2	B	2, B
12
13			^^ Not needed
Sheet401

Cell Formulas
Range	Formula
E4	=LOOKUP(2,1/FIND(" "&D4&" "," "&A$4:A$11&" "),B$4:B$11)

Formula copied down.

 

[tonyg88]

thanks! seems to work, but confused how it works. What is the purpose of the "2,1" in the lookup function? also, what is the purpose of the "" and & symbols?

Also, the original list is a lot longer and has more IDs / letters, do you think the formula would still apply?

 

[jtakw]

We're looking up the Value of 2 within the FIND results divided by 1 (which will Never be greater than 1), so the LOOKUP will return the Last found Value (which is what you want).
The SPACE " " in front and behind the Lookup Value and Lookup Array prevents Errors for the Lookup, see my sample Row # 9, which is 11 (eleven), Not 1 (One), without the SPACES, the LOOKUP will return the incorrect result of "B".

Don't see your actual data, can not answer definitively, but it should work.