Formula to select a cell that has a different value than the rest of the cells in that column

jt145

New Member
Joined
Jan 16, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a simple three cell range W4:W6 two of the cells have the text "No task Id Found" and the other one has a number value such as "102345678".

i would like to be able to have a formula in a now empty Cell such as A1, that searches that small range and returns the one that doesn't have the text "No task Id Found"

But im banging my head on the wall with this one.

here is the "table" I have and the small range im trying to search through is the "Results:" Column

1610813142938.png


the reason for this is that some times the B column will have the 102345678 type number in it and some times the C could and even the A might but it will only ever be 1 column with it .
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,504
Office Version
  1. 365
Platform
  1. Windows
Do you want to return the value of the cell, or the column it was found in?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,504
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=FILTER(W4:W6,W4:W6<>"No task Id Found")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,504
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,491
Messages
5,625,075
Members
416,068
Latest member
seba_s

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
Top