VLOOKUP PROBLEM

[AydanTarverdiyeva]

Hi. I have problem with excel but i cant explain. please help me

 

[SanjayGMusafir]

I can understand that you don't want to sort apple and banana - recently we had a similar thread on the forum

Draw some clues from it and just in case you need further help, be open to post your query here.

Check this -

Splitting IDs using one reference column

Hi All, I need to create two separate columns (Parent & Child) from a Single column. Can anyone help me on this attached the sample input and output format. Thanks in advance !!

www.mrexcel.com

 

[Fluff]

@SanjayGMusafir the OP is using 2019 & therefore cannot use either Unique or Xlookup.

 

[SanjayGMusafir]

Just as @Fluff mentioned you are using Excel 2019 in that case you have rely on INDEX-MATCH not sure if VLOOKUP or LOOKUP will serve the purpose.

But if you share some 'close to' or real sample data using XL2BB probably some solution can be derived.

 

[AydanTarverdiyeva]

Thank you but i have not unique or xlookup formulas.

 

[AydanTarverdiyeva]

Just as @Fluff mentioned you are using Excel 2019 in that case you have rely on INDEX-MATCH not sure if VLOOKUP or LOOKUP will serve the purpose.

But if you share some 'close to' or real sample data using XL2BB probably some solution can be derived.

Thank you but i have not unique or xlookup formulas.

 

[SanjayGMusafir]

Thank you but i have not unique or xlookup formulas.

See mention in thread #4 - Share some sample data to give you an adequate solution.

 

[AhoyNC]

You can use the remove duplicates under the DATA ribbon to get the data in column C.
Then try the formula below.
With your version of Excel you will probably need to enter the formula with CTRL-SHIFT-ENTER.
Then move across and down as needed.

Book1
	A	B	C	D	E	F	G	H	I
1
2	apple	2017			Year
3	apple	2018			apple	2017	2018
4	sugar	2019			sugar	2015	2016	2019
5	sugar	2016			banana	2014	2015	2017	2018
6	sugar	2015
7	banana	2014
8	banana	2015
9	banana	2017
10	banana	2018
Sheet1

Cell Formulas
Range	Formula
F3:I5	F3	=IFERROR(SMALL(IF($A$2:$A$10=$E3,$B$2:$B$10),COLUMNS($F$2:F2)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AhoyNC]

Along with the formula I gave you in post#8 above, here is a formula using the AGGREGATE function that does not require CTRL-SHIHT-ENTER. Just ENTER.
Just drag the formula down and across as needed.

Book1
	A	B	C	D	E	F	G	H	I
1
2	apple	2017			Year
3	apple	2018			apple	2017	2018
4	sugar	2019			sugar	2015	2016	2019
5	sugar	2016			banana	2014	2015	2017	2018
6	sugar	2015
7	banana	2014
8	banana	2015
9	banana	2017
10	banana	2018
Sheet1

Cell Formulas
Range	Formula
F3:I5	F3	=IFERROR(AGGREGATE(15,6,IF($A$2:$A$10=$E3,$B$2:$B$10),COLUMNS($F$2:F$2)),"")

 

[AydanTarverdiyeva]

You can use the remove duplicates under the DATA ribbon to get the data in column C.
Then try the formula below.
With your version of Excel you will probably need to enter the formula with CTRL-SHIFT-ENTER.
Then move across and down as needed.

Book1
	A	B	C	D	E	F	G	H	I
1
2	apple	2017			Year
3	apple	2018			apple	2017	2018
4	sugar	2019			sugar	2015	2016	2019
5	sugar	2016			banana	2014	2015	2017	2018
6	sugar	2015
7	banana	2014
8	banana	2015
9	banana	2017
10	banana	2018
Sheet1

Cell Formulas
Range	Formula
F3:I5	F3	=IFERROR(SMALL(IF($A$2:$A$10=$E3,$B$2:$B$10),COLUMNS($F$2:F2)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you very much! i have one more question. Is this formula can be use for not only numbers or years, for texts? i have same problem with that. Now i have no years but texts