Matching numbers from columns and return text for a given input

insaneuou

New member
Joined
Dec 12, 2012
Messages
1
Reaction score
0
Points
0
Hello all,

I am having an issue regarding matching an input values and returning a text. Been trying different formulas but cannot get it right.

Let me explain the issue:

Consider the following as a simple example:

  • Lets say, I have 4 columns, each column has more than 100 rows (numeric values - 6digit).
  • Number of rows in each column varies, eg: column 1 - 200 rows, column 2 - 342 rows and so on.
  • There is an INPUT CELL and 4 Different CELLS (lets say P, Q, R, S)
  • Once I enter value in INPUT CELL,
  • OUTPUT IN CELL 'P' = scan all values in COLUMN 1; If match is found Return 'SAMPLE TEXT1' if no match is found return 'SAMPLE TEXT 2'
  • Similarly with OUTPUT IN CELLS 'Q', 'R', 'S'.

I am attaching a sample file for reference.
View attachment sample.xls

Any help with the issue is highly appreciated.

Thanks in advance.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Assuming the input cell is P1, in P2 enter

="SAMPLE TEXT "&IF(ISNUMBER(MATCH(P1,A:A,0)),1,2)

and copy across.
 
Top