How to return multiple lookup values without using FILTER formula

icytuvi

New Member
Joined
Feb 2, 2020
Messages
30
Office Version
  1. 2013
Platform
  1. Windows
Hi,

I have the data table as below and I would like to list out names of states matching "up" or "down" value.

Filter formula does not work in my excel version so I would like to seek your help with another alternative formula that works.

Thank you very much.

Sample.xlsx
BCDEFGHIJKLMN
11Product 1Product 2Product 3Product 4Product 5Product 6Product 7Product 8updown
12California- down - down - down - down Product 1Florida, Hawaii, IdahoIowa, Kansas, Maine
13Colorado- up down ---- up Product 2
14Florida up -- up up ---Product 3
15Hawaii up down up --- up -Product 4
16Idaho up -- up down ---Product 5
17Iowa down up --- down -Product 6
18Kansas down --- up -- down Product 7
19Maine down -- down --- up Product 8
20Maryland- down ------
Sheet1
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi

Please check this- this is for Product 1 to Product 3, and could be extended for n number of products

Made some dummy (helper columns)- F:H (3 columns for 3 products)- for UP, and J:L for DOWN

These sections could be kept as separate sheets for ease of understanding later on

Wherever match is made, the dummy columns would return the states

Concatenating these states and removing leading blanks and trailing commas would lead to the final result

HLOOKUP of this would yield in the result in the fashion you require

Please do let know if this has been of help, thanks

Cell Formulas
RangeFormula
F2:H10F2=IF(B2="Up"," "&$A2&",","")
J2:L10J2=IF(B2="Up"," "&$A2&",","")
O2:O4O2=HLOOKUP($N2,$F$11:$H$14,4,0)
P2:P4P2=HLOOKUP($N2,$J$11:$L$14,4,0)
F12:H12,J12:L12F12=CONCAT(F2:F10)
F13:H13,J13:L13F13=REPLACE(F12,1,1,"")
F14:H14,J14:L14F14=REPLACE(F13,LEN(F13),1,"")
 
Upvote 0
Solution
Hi

Please check this- this is for Product 1 to Product 3, and could be extended for n number of products

Made some dummy (helper columns)- F:H (3 columns for 3 products)- for UP, and J:L for DOWN

These sections could be kept as separate sheets for ease of understanding later on

Wherever match is made, the dummy columns would return the states

Concatenating these states and removing leading blanks and trailing commas would lead to the final result

HLOOKUP of this would yield in the result in the fashion you require

Please do let know if this has been of help, thanks

Cell Formulas
RangeFormula
F2:H10F2=IF(B2="Up"," "&$A2&",","")
J2:L10J2=IF(B2="Up"," "&$A2&",","")
O2:O4O2=HLOOKUP($N2,$F$11:$H$14,4,0)
P2:P4P2=HLOOKUP($N2,$J$11:$L$14,4,0)
F12:H12,J12:L12F12=CONCAT(F2:F10)
F13:H13,J13:L13F13=REPLACE(F12,1,1,"")
F14:H14,J14:L14F14=REPLACE(F13,LEN(F13),1,"")
Thank you very much for your solution. I've been waiting for a few days for the answer and thought that this post would sink, so in the mean time, I tried looking for solution on Google and it came out with a two step solution by first listing out all the matching values by using INDEX-AGGREGATE and secondly to join those texts together (I had a later thread on this forum too seeking for help on joining text :D) I'll try with your suggestion
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top