assign Incremental numbers in new column based on criteria from two columns

[mena139]

I am looking for assistance in setting up a formula to return incremental values to a list (including duplicates) only when selected values are present in a different column. Please see the example attached,

if the produce is apples, banana or peach assign the number (1,2,3) representing the city it comes from.

 

[Ummm1]

It's not clear to me what you are trying to do. Can you explain a bit more?

 

[mena139]

I want the formula to return the following in the last column. the idea being that if the produce listed at the bottom comes from new york it will assign a "1". if it comes from chicago it will assign a "2"
if it comes from atlanta then it will assign a "3". at the same time though if the value in the second column is not on the list of produce (ie tomatoes) then leave third column blank

 

[Toadstool]

Hi Mena139,

I think this does what you want but what should happen if the city is not in the list?

Book2
	A	B	C
1	City	Produce	Value Code
2	New York	apples	1
3	New York	banana	1
4	New York	peach	1
5	New York	tomato
6	Chicago	apples	2
7	Chicago	banana	2
8	Chicago	peach	2
9	Chicago	tomato
10	Atlanta	apples	3
11	Atlanta	banana	3
12	Atlanta	peach	3
13	Atlanta	tomato
14
15
16	Only If	Cities
17	apples	New York	1
18	banana	Chicago	2
19	peach	Atlanta	3
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=IF(ISNA(MATCH(B2,$A$17:$A$19,0)),"",INDEX($C$17:$C$19,MATCH(A2,$B$17:$B$19,0)))