Find cell value in range (multiple columns) and return column number.

[vineet78]

Hi

I am trying to find the excel formula which will search a value in Range of cells (say 4-5 columns) and then return the column number.

Any suggestions please?


Regards
Vineet

 

[Special-K99]

=MATCH("smith",A1:D1,0)

where A1:D1 are the columns being searched and "smith" is the name to search for

 

[vineet78]

No, it returns the value #N/a, i think match column does not work with more than 1 column.

 

[Special-K99]

"I am trying to find the excel formula which will search a value in Range of cells (say 4-5 columns) and then return the column number."

=MATCH("smith",A1:D1,0)

The value to be searched for is "smith"
The range A1:D1 consiss of 4 columns.

This reflects what you've asked for and works perfectly and returns 2 since I put smith in B1 and column B is column number 2.

If you are getting #N/A then the value you are searchibng for is not in any of the columns.

Supply some example data of what you are searching for and the data you are searching since my solution works.

 

[vineet78]

Yes you are right, actually i was selecting the full columns and not just range.
For example, i chose A:D instead of A1:D1, does that mean we cannot select full columns range in match ?

 

[Special-K99]

Selecting whole columns is not advisable as it slows formulas down.
This only works for a given row.
I'll see if I can come up with something for whole columns

 

[Special-K99]

I've got something like this already set up.

This works

=MAX(IF(A:D="smith",COLUMN(A:D)-COLUMN(A1)+1))
Array formula, use Ctrl-Shift-Enter

 

[vineet78]

I dont prefer using Array formulae because not many people understand them
Thanks for your help.

I am facing another issue with new code, can you please provide your guidance.
Actually I am trying to paste numbers from excel into word document table , Please note that this is table to table pasting but only range of numbers from excel to word.
Do you have any suggestions please in vba.

thanks in advance.