How to Convert this Code to Look for words instead of numbers

[trevor2524]

Hello Everybody, I have the following code

Please Login or Register  to view this content.

from another macro i made. It takes a number in Sheet 1 and then checks in Sheet 2 columns C and D to see if it finds that number located at all. If it does then on Sheet 1 in Column F it will display the information found on sheet 2 in Column C. I'm trying to figure out how do I convert this formula to look for words instead on numbers but with the same kind of format. Pull it from sheet 1 and check in sheet 2. The same columns it will be checking except instead of just displaying the information from column C on Sheet 2 it will need to display Column B and C from Sheet 2 on Sheet 1 starting in Column F. Thanks for your help.

[Ron Coderre]

I *think* this regular formula will work for numbers OR words

Please Login or Register  to view this content.

Note: That formula assumes that, if there is a match...there is only ONE.

Is that something you can work with?

[trevor2524]

Formula Error.PNG Thanks for the response. I don't know if i did something wrong but this is the error message I get when I try to insert that formula. The reason why I'm trying to see if I need to convert something is because when i insert the original formula into the cell i get back #REF!.

[Ron Coderre]

My apologies...I didn't have a chance to test the formula.
Try this variation:

Please Login or Register  to view this content.

Is that something you can work with?

[trevor2524]

Hey Ron, Thanks for the help. That formula somewhat works but unfortunately the coding is puling the first row instead of the row that it finds the match in. So it found a match in row 9 in column c but it is displaying the value found in column C row 1 on sheet 2 when it should be displaying the row that the match is found and the information from Column C and D. is this possible?

[Ron Coderre]

I'm not seeing that behavior in my mock-up.
Can you post a sample workbook that demonstrates the problem?

[trevor2524]

Hey Ron, It Seems to be working except the one thing I'm trying to do is have it pull the information from Columns A and B on sheet 2 when it finds a match. Right now i switched it so it pulls from Column B, Do you know how to add column A into the mix?test project.xlsm

[Ron Coderre]

Using your posted workbook...here are two approaches:
1) A regular formula (not an array formula) that returns just Col_A matches

Please Login or Register  to view this content.

2) A regular formula (not an array formula) that returns concatenated Col_A/Col_B matches

Please Login or Register  to view this content.

One other thing...
If you convert the lookup range to an Excel Table (see the attached file):
1) you can reference the table fields directly
2) the table will automatically expand and contract to accommodate the data
3) you won't need to make Excel go through the work of calculating on 70,000 rows

So the concatenated field formula would be:

Please Login or Register  to view this content.

Yes, it's more verbose, but it's also very descriptive.

Does that help?