reference cell in Match

[mcauliff]

I'm attempting to find a entry in a worksheet using a column from another worksheet. Example; Sheet1 column 20 contain 12345. Is this number in Sheet 2 column 5.

I have tried the CountIF, Vlookup and Match. I sort of got the Match to work, but have a problem with reference the cell.

=MATCH(AJ104,ProdITG!E2:E1307,0), this returns an error.

If I code =Match('12345",ProdITG!E2:E1307,0), it will return the row prior to the record.

How should it be coded to use the cell reference?

ProdITG is Sheet2.

Sheet1 has about 3800 rows and Sheet2 had 1307 rows.

 

[SkipVought]

Hi,

NUMBERS and STRINGS are vastly different. You cannot use a NUMBER 1 to lookup a STRING 1.

So when you use a STRING to do the lookup, "...it will return the row prior to the record."

No, it returns the row OFFSET from the START of the range, which starts in ROW 2.



Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[mcauliff]

Skip

Thank you for the response.

I'm assuming from you response that Match will not work for what I'm attempting to do.

 

[SkipVought]

Sure it will, as long as AJ104 contains a matching STRING.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[yogia]

Hi mcauliff:

Try ...

=MATCH(12345,ProdITG!E:E,0)

and preferably ...

=MATCH(refCell,ProdITG!E:E,0)

where refCell houses the entry to be matched in the column number and sheet number of interest.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

The fact that [blue]Match('12345",...[/blue]* returned a result tells us that the data in [blue]ProdITG!E2:E1307[/blue] is text.

So to use a cell reference, you need to either change the data in [blue]ProdITG!E2:E1307[/blue] to numbers or make the data in [blue]AJ104[/blue] text.

Assuming that these are some sort of identifiers that you won't be doing arithmetic with, then they should be stored as text.

So in cell [blue]AJ104[/blue], type in [!]'12345[/!]. The single quote at the beginning will not be displayed. It just tells Excel to treat what follows as text.

As Skip hinted at - if the number that was returned was one lower than you expected, change the lookup_array to start at row1 - [blue]ProdITG!E[highlight]1[/highlight]:E1307[/blue]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mcauliff]

Everyone,

Thank you for your replies.

I usually don't work in Excel, except fro basic tasks.

How would I convert 3708 rows with the number to text in the 1 column? I hope I don't have to edit each seperately.

 

[SkipVought]

sub ConvertNbr2Txt()
dim r as range
for each r in range([A1], [A1]/end(xldown))
  with r
    if isnumeric(.value) then .value = "'" & .value
  end with
next
end sub

paste in a module and run.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[AnotherHiggins]

If you're not comfortable with macros, you could use a formula in a different column. Something like this:
[tab]=Text(A2,"@")

Fill the formula down for all used rows.

Copy the column containing the formulas, Copy.

Select the column with the values (in our example, column A) and Paste Special > Values.

Delete the column with the formulas.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mcauliff]

Thanks to everyone.

using your suggestions, I have it working.

I have learn something new today. I will file for future reference.