search data in one column and return first result from another column.

[davids4500]

search a column of data for "Air Freshener" (E2:E6550) and when the first correct result, return the first date that appears from another column (N2:N6550) where it is not empty.

 

[arthurbr]

Vlookup and return first value in list that is NOT NULL

Hi everyone, I have an issue where my VLOOKUP alone does not work as it will always return the first entry in my table. In my case I have several entries for..

techcommunity.microsoft.com

 

[Peter_SSs]

My interpretation of what you might want is different. Is this what you mean?
If neither suggestion is what you want then perhaps a simple set of sample data and the expected results with XL2BB would help clarify.

24 01 13.xlsm
	E									N	O	P	Q
1												Item	First Date
2	a											Air Freshener	18/07/2023
3	b									15/06/2023		Detergent	23/08/2023
4	c											Other	23/08/2023
5	Air Freshener
6	e
7	f
8	g									18/07/2023
9	h									19/07/2023
10	Air Freshener
11	Detergent
12	k
13	Other									23/08/2023
14
First not empty

Cell Formulas
Range	Formula
Q2:Q4	Q2	=INDEX(N:N,AGGREGATE(15,6,ROW(N$2:N$6550)/((ROW(N$2:N$6550)>=MATCH(P2,E:E,0))*(N$2:N$6550<>"")),1))

 

[davids4500]

My interpretation of what you might want is different. Is this what you mean?
If neither suggestion is what you want then perhaps a simple set of sample data and the expected results with XL2BB would help clarify.

24 01 13.xlsm
	E									N	O	P	Q
1												Item	First Date
2	a											Air Freshener	18/07/2023
3	b									15/06/2023		Detergent	23/08/2023
4	c											Other	23/08/2023
5	Air Freshener
6	e
7	f
8	g									18/07/2023
9	h									19/07/2023
10	Air Freshener
11	Detergent
12	k
13	Other									23/08/2023
14
First not empty

Cell Formulas
Range	Formula
Q2:Q4	Q2	=INDEX(N:N,AGGREGATE(15,6,ROW(N$2:N$6550)/((ROW(N$2:N$6550)>=MATCH(P2,E:E,0))*(N$2:N$6550<>"")),1))

Thank you, Peter, maybe i didn't explain well enough. Product Name is actually Column "E", the column Supplier orders is actually Column "M" and the data field I require is Column N. So if there is the product "Air Freshener present in "E" (not always) and a supplier order present in M then return the first date that is showing for a supplier order Column N...I hope that is clearer. Thank you

 

[Peter_SSs]

maybe i didn't explain well enough.

Yes, I think not. It wasn't clear that "Air Freshener" could have other text with it in the same cell, and there was no mention that a supplier order needed to exist.

Also ..

• Where should the results be?
• What is the name of the table this data is in?
• I cannot copy from an image to test so ..

.. perhaps a simple set of sample data and the expected results with XL2BB would help ..

 

[davids4500]

Dear Peter SSs. Thank you for your help. I am having issues with the xl2bb not opening in protected view. I have attached a table. I have changed the columns and reduced the clutter.
Product Name is now Column "C", the column Supplier orders is now Column "F" and the data field I require is Column G. So if there is the product (For example) "Air Freshener (which also has other text in the field) present in "C" (not always) and a supplier order (Which must be present) in column "F" then return the first date in Column G .I hope that is clearer. Thank you. I will be using the formula in a spreadsheet on worksheet Keycategories!S2. Thank you.

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H
AFR065	204872	Air Freshener Truck Don't Look Back (Ocean)	15/01/2024		12	15/01/2024
AFR157	255927	Air Freshener Beautiful Girl - Strawberry	15/01/2024		192	15/01/2024	30/05/2024
AFR157	255927	Air Freshener Beautiful Girl - Strawberry	30/05/2024		96	15/01/2024	30/05/2024
AFR172	256671	Air Freshener Make Difference Today	15/01/2024		192	15/01/2024
AFR191	257962	Air Freshener Stay Close	15/01/2024		192	15/01/2024	30/05/2024
AFR191	257962	Air Freshener Stay Close	30/05/2024		48	15/01/2024	30/05/2024
AFR192	257963	Air Freshener Sometimes	15/01/2024		192	15/01/2024
AFR205	258599	Air Freshener Pink Heart - Strawberry	15/01/2024		192	15/01/2024
AFR209	258603	Air Freshener Be Happy Van Orange	30/05/2024		240	30/05/2024

 

[Peter_SSs]

I am having issues with the xl2bb not opening in protected view.

Have a look at the suggestions in this thread.

Where should the results be?

Did you answer this point? Are the expected results shown in post #6? If not can you manually fill in the expected results and post them?

What is the name of the table this data is in?

Doesn't seem that you answered this point either?

I will be using the formula in a spreadsheet on worksheet Keycategories!S2

What is the name of the worksheet the above data is in?

 

[davids4500]

Have a look at the suggestions in this thread.


Did you answer this point? Are the expected results shown in post #6? If not can you manually fill in the expected results and post them?


Doesn't seem that you answered this point either?


What is the name of the worksheet the above data is in?

The data provided is from worksheet ChreosStockarriving.
The expected result for the product 'Air freshener' is 15/01/2024 because it is the first date in the column that has an order for that product (Column F)

 

[Peter_SSs]

Did you look at the XL2BB link that i suggested?


Thanks for the additional information. That just leaves this question

What is the name of the table this data is in?

From the image in post #4 it appears that the data is in a formal Excel table. What is the name of that table?
It can be found in the Name Manager section of the Formulas ribbon tab.

 

[davids4500]

Hi.
The name of the table is ChreosStockArriving.
I have looked at the link XL2BB and working on it...
I might get some help from my neighbour.
I appreciate your persistence.
Kindest regards
David