Comparing columns

[Rich Mogy]

I have the following in a spreadsheet

My Company 1 56346 56346 56346
My Company 2 56850 56850 56850
My Company 3 73130G 73130 73142
My Company 4 73130G 73142 73130


Is there are function that will compare the first five characters in column
B with column D and return either the row number of the match or the
information in column A of the same row.

Ex. -- I will provide 56346. I want the function to return "My Company 1"
But, if I provide 73130, I want "My Company 4" not "My Company 3"

In all cases, ignore column C

Thanks in advance,

Rich Mogy

 

[Pete_UK]

Yes, you want an INDEX(...MATCH(...)...) combination. The inner MATCH
function will be looking at column D trying to find a match with your
search value. If there is one then the relative row in the range is
returned, and the INDEX function allows you to get the contents of
column A on that matching row. Excel help describes the use of both
functions quite well.

Hope this helps.

Pete

 

[Roger Govier]

Hi Rich
Try
=IF(ISERROR(MATCH(G1,D14,0)),"",MATCH(G1,D14,0))

 

[Rich Mogy]

Thanks to both who replied, however, I guess I wasn't clear with my example.

Column A has My Company 1, 2, 3, 4, etc.
Column B has a 5 or 6 character entry.
Column C is extraneous
Column D has a 5 character entry, that may or may not match to the first
five characters in column B.

I want to compare three things --- left(column B,5) to Column D to an input
number and return column A.

So if I input 56346, I want either the row number (1) or "My Company 1"
returned.

But if I input 73130, I want either the row number (4) or "My Company 4"
returned, since only row 4 meets the criteria (row three doesn't because
column d is 73142 even though the first five characters in column B are
73130.

Hopefully this is clearer.

Rich

 

[Roger Govier]

Hi Rich

In my solution, I was placing the input value in cell G1.
Because there is only a single occurrence of 7130 in column D, it will
quite rightly return the answer of 4, there is no need for the extra
test.
However, if you are saying that column D could have duplicate values,
but with different values in the first 5 characters of column B, then I
would create a helper column. I used column E with the formula in E1
=D1*(--LEFT(B1,5)=D1)
Copy down through E2:E4
The formula to return the row number is then
=IF(ISERROR(MATCH(G1,E1:E4,0)),"",MATCH(G1,E1:E4,0))
or to return My Company 4 it is
=IF(ISERROR(MATCH(G1,E1:E4,0)),"",INDEX(A1:A4,MATCH(G1,E1:E4,0)))

 

[Rich Mogy]

Thanks Roger,

You have led me down the right path.