CODEWITHSUNDEEP
excel
Mistie
Mistie

Reputation: 11

Pulling in the next value in a column

I have a column with numbers and a column with country locations. I want the formula to go down the country location column and produce any number that is associated with "USA." I can get the first value, but because "USA" isn't a unique value within the column I'm referencing, it always and only produces the first number. How do I get it to continue pulling the next number down the row? In the below example, I want the first result to be 56436, the next to be 50131, and so on. What is the formula that needs to be written so that it continues down the column?

Number      Country
00080462    Portugal
00080132    Portugal
00080183    Portugal
00080187    Portugal
00056436    USA
00092002    United Kingdom
00050131    USA
00056556    USA
00057100    USA
00057938    USA
00051993    USA
00055780    USA
00091879    United Kingdom
00052248    USA
00056019    USA
00091944    United Kingdom
00025547    USA
00091977    United Kingdom

Upvotes: 1

Views: 66

Answers (2)

p._phidot_
p._phidot_

Reputation: 1952

Assuming your sample data+header is in A1:B17 type this in D2, then drag downwards. : 

=IFERROR(IF(ROWS($A$2:$A2)>1,INDEX(OFFSET($A$2:$A$19,MATCH(D1,$A$2:$A$19,0),0),MATCH("USA",OFFSET($B$2:$B$19,MATCH(D1,$A$2:$A$19,0),0),0)),INDEX($A$2:$A$19,MATCH("USA",$B$2:$B$19,0))),"")

Hope that helps.

Upvotes: 1

user4039065
user4039065

Reputation:

try this at the top of an unused column and fill down,

=index(a:a, aggregate(15, 7, row($2:$20)/(b$2:b$20="usa"), row(1:1)))

Upvotes: 2

Related Questions