Horizontal Search

[Nickmsi]

Hi . . .

I am having trouble trying to find the right formula to search each horizontal row for the number "4".

I need the formula to return the Column that the "4" is in or the Columns as sometimes there is more than one "4" in the row.

Column A in the attached sheet is where the formula needs to go and we search the columns C to AH for each row.

Thanks

Nick

[Tony Valko]

For multiple results in a single cell you'll need some form of VBA to do this.

Select any sheet tab
Right click>View Code
Open the Project Explorer (if it's not already open): CTRL R
In the Project Explorer pane on the left, locate your file name. It'll appear like this: VBAProject(SearchTest.xlsx)
Select the file name then right click
Select Insert>Module
Copy the code below and paste it into the window that opens:

Please Login or Register  to view this content.

Go back to Excel: ALT Q

Now, enter this array formula** in A2 and copy down to A139:

=SUBSTITUTE(TRIM(aconcat(IF(C2:AH2=4," "&C$1:AH$1,"")))," ",", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Here's your file with this applied:

SearchTest.xlsm

Since the file now contains VBA code it has to be saved as a macro enabled file in the file format *.xlsm.

[Nickmsi]

Works like a charm . . .

thank you so much, you have save me a lot of time

Cheers

Nic

[Tony Valko]

You're welcome. Thanks for the feedback!