conditional lookup array formula

Question

Formula to find:

Look up a text value from a Table Column range and find corresponding values from 2 column ranges basing on a criteria of a column numeric value.

Example: Lookup Value placed in outside table range>$J$4> Table range B4:H100>Lookup column(6) range F4:F100> find corresponding values in(col 8) range > H4:H100 > criteria: which are greater than zero > Return corresponding values from C4:C100 range(Col 3) >output from J5:J.

Answer 1

Hi

After carefully reading the details of your scenario and goals,

It seems you are looking to Filter your table by Product Name and Sales greater than 0 (zero)

If so, the are a few options to achieve that.

Option1= Formula Solution

If you are using Excel from Office 365 version then you may try the FILTER formula.

https://www.youtube.com/watch?v=Eehk6PC0oGs&t=315s

OTHERWISE

https://www.youtube.com/watch?v=fDB1Ktyhp3Y&t=716s

Option 2= Advanced Filter

https://www.youtube.com/watch?v=VqQACB_69SQ&t=369s

Option 3= Power Query

https://www.youtube.com/watch?v=TX6mmJRwpg8&t=301s

https://www.youtube.com/watch?v=oVZW1gCObHY

https://www.youtube.com/watch?v=8MlDYKPxznQ&t=409s

Choose the option you prefer and come back to us if you have any troubles

Regards

Jeovany

Answer 2

Hi

After carefully reading the details of your scenario and goals,

It seems you are looking to Filter your table by Product Name and Sales greater than 0 (zero)

If so, the are a few options to achieve that.

Option1= Formula Solution

If you are using Excel from Office 365 version then you may try the FILTER formula.

https://www.youtube.com/watch?v=Eehk6PC0oGs&t=315s

OTHERWISE

https://www.youtube.com/watch?v=fDB1Ktyhp3Y&t=716s

Option 2= Advanced Filter

https://www.youtube.com/watch?v=VqQACB_69SQ&t=369s

Option 3= Power Query

https://www.youtube.com/watch?v=TX6mmJRwpg8&t=301s

https://www.youtube.com/watch?v=oVZW1gCObHY

https://www.youtube.com/watch?v=8MlDYKPxznQ&t=409s

Choose the option you prefer and come back to us if you have any troubles

Regards

Jeovany

Thank you Jeovany. These vedios solved my problem within seconds.

Answer 3

Formula in J2

=IFERROR(INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW($D$2:$D$100)-ROW($D$2)+1)/($D$2:$D$100=$K$1),ROW(1:1))),"")

Formula in K2

=IFERROR(INDEX($G$2:$G$100,AGGREGATE(15,6,(ROW($D$2:$D$100)-ROW($D$2)+1)/($D$2:$D$100=$K$1),ROW(1:1))),"")

Drag down both formulas in your range.

Answer 4

	A	B	C	D	E	F	G
1	S NO	Sales Man	unit price	Product	Month	Brand	SALES
2		Mehta	30000	Lap Top	JULY	Dell	200000
3		Kapoor	2000	Ups	JULY	ElectCorp	10000
4		Vijay	30000	Lap Top	JULY	Dell	0
5		John	2000	Ups	JuLY	ElectCorp	30000
6		Patel	30000	Lap Top	JULY	Dell	350000

this is dummy data.

Sales men sales data if lookup value is Lap Top > J5 ---------K5

                                                                      Mehta  ------200000

                                                                      Patel    ------ 350000

                               if lookup value is Ups   >Kapoor------10000

                                                                       John ---------30000

Answer 5

Please post a screenshot of your data and give a sample result (dummy data is preferable)