Return a row value based on conditions

**roman22** · 11-20-2019, 10:39 PM

HI All,

I have been trying to get a formula working for the below scenario but couldn't succeed.

There are 2 worksheets in my file. Sheet 1 has list of suburbs and Sheet 2 has list of same suburbs with a Unique ID for them.

There is one to many relationship between suburbs from Sheet 1 and Sheet 2.

For Example,
Sheet 1 has Suburb = "Hanwood North" and Sheet 2 has same suburb with 4 rows and Unique ID for each row.
I need to pick second row's Unique ID for the same suburb.

Sheet 1 :

Suburb
Hanwood North

Sheet 2:

Suburb ID
Hanwood North | S04-2ALZ-5106
Hanwood North | S03-2ALZ-5106
Hanwood North | S02-2ALZ-5106
Hanwood North | S01-2ALZ-5106

**Gregb11** · 11-20-2019, 11:44 PM

So you want to get the 2nd match on the suburb? This means on sheet 1 of your attachment, next to Hanwood North you want "S02-2ALZ-5106" to display? If so, you can use this array formula in B2 and copy it down. (To enter an Array formula, click in the cell, paste the formula, and then instead of hitting Enter, hit Shift, Ctrl, Enter at the same time.

=INDEX(Sheet2!B2:B7,SMALL(IF(Sheet2!A2:A7=A2,ROW(Sheet2!A2:A7)-ROW(INDEX(Sheet2!A2:A7,1,1))+1),2))

If you do it right, the formula above will show with brackets around it ( {} ).

**menem** · 11-21-2019, 12:09 AM

Please try

B2 on Sheet1

Please Login or Register  to view this content.

Regards.

Note : For better performance, you may limited refered range of sheet2 by change ALL Column to Range ie.
From Sheet2!$B:$B to Sheet2!$B$1:$B$10 and so on.

Return a row value based on conditions

LinkBack

Thread Tools

Rate This Thread

Display

Return a row value based on conditions

Re: Return a row value based on conditions

Re: Return a row value based on conditions

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Return value based on the match & If conditions

Return Value Based on Two Conditions

Return value based on a number of conditions

[SOLVED] Lookup and Return Value Based On 2 Conditions

return count value based on several conditions

return value based on two conditions

[SOLVED] How do I return a value based on multiple possible conditions?

Bookmarks

Bookmarks

Posting Permissions