Retrieve data using index-match formula

Hi friends,

I want to retrieve the address from ‘Personal’ sheet to ‘Label’ sheet.
If there is a ‘Expired’ or ‘Duplicate’ or ‘Company’ remark in column ‘W’ then I want to skip that serial no.
For ex. Here there is a ‘Expired’ remark in cells ‘W10, W16, W17, W22, W25’ So I want to skip serial nos. 2, 8, 9, 14, 17 on ‘Label’ sheet.
I don’t want the serial numbers on ‘Label’ sheet more than the serial numbers on the ‘Personal’ sheet.
I want to convert formulas into values on ‘Label’ sheet.

Any help will be highly appreciated.

Thanking you.

Add a helper column to the data to make this simple:

Personal
AL1: "Key"
AL2: =IF(OR(W9="Duplicate",W9="Expired",W9="Company"), "", B9)

Copy AL2 down the data set

Label
A1: =IFERROR(SMALL(Personal!$AL:$AL,ROW(A1)*2-1), "")
C1: =IFERROR(SMALL(Personal!$AL:$AL,ROW(A1)*2), "")

Copy down.

Hi JBeaucaire,

Greetings from me!

It's working fine on 'Personal' sheet but I'm not getting addresses on 'Label' sheet.

I have started "Key" from AL8 and formula from AL9 because all of my data starts from row 8 and there is a data of company name and other information. So rows 1-7 are not blank.

I want to change the formula to value to reduce the size of file.
I don't want extra serial numbers on 'Label' sheet. It should match with the serial numbers on the 'Personal' sheet. In short the serial numbers on 'Label' sheet should not be greater than the serial numbers on 'Personal' sheet. In our case now there are 21 members on 'Personal' sheet so that there should be only 21 serial numbers on 'Label' sheet.

I think a macro will be better to achieve this target.

Thanking you in anticipation.

Your KEY column went down too far. Your data stops at row 29.

Hi JBeaucaire,
Excellent! Now it's working fine.

I appreciate you for the same.

Thank you and have a nice time.