Using: Excel 2007 I'm working with the current formula: =IF(ISNA(VLOOKUP(A3,Products!B:B,2,FALSE))," ","Y")

To make this simple, lets say I have TAB 1 and TAB 2.

TAB 1 Column A contains part of the numbers/letters which I need to find in TAB 2 Column B.

Ex: TAB 1 says 1005

TAB 2 says AZB1005What I'm trying to do is, if TAB 2 contains a number that matches TAB 1, I want the excel to say "Y"; if not, just leave the cell blank.

So far, I've gone as far as using the formula =value(right(a2,4)) to acquire last 4 values and turn it into an actual VALUE or else it wasn't recognizing what was in TAB 2.

I hope I have all the information here.

Thank you in advance.

✔ Best Answer

Try this, if your data looks like this: Sheet 2:

A B 1) AZT7441XL 20 2) AZT7442XL 30 3) AZT7443XL 40 4) AZT7444XL 50 5) AZT7445XL 60 6) AZT7446XL 70 7) AZT7447XL 80 8) AZT7448XL 90 9) AZT7449XL 100 10) AZT7450XL 110 11) AZT7451XL 120On Sheet 1 cell A1 enter your search string: 7445XL

On Sheet 1 cell B1 enter the formula:

=SUMPRODUCT(--(ISNUMBER(SEARCH("*"&A1&"*",Sheet2!A1:A11))),(Sheet2!B1:B11))This will return the contents of the corresponding cell in Sheet 2 Column B.

So, using the above search string it should return 60.

MIKE

Try using a SUMPRODUCT() function and see how that works: With your search string on Sheet 1 cell A3: 1005

and your target range to search on Sheet 4 cells A3 through A14

this will return the corresponding data from Sheet 4 cells B3 through B14=SUMPRODUCT(ISNUMBER(SEARCH("*"&A3&"*",Sheet4!A3:A14))*(Sheet4!B3:B14))

Modify to suit your needs.

~~Only~~caveat is,

if the search string occurs more than once, SUMPRODUCT() will add the two return values.EDIT ADDED:

Second caveat, return value must be a number, else you get an error msg.

MIKE

Well, before I even try to figure out what your goal is, I can say straight off that your VLOOKUP formula is incorrect: VLOOKUP(A3, Products!

B:B, 2, FALSE)Your table_array consists of a single column (B) yet you are asking the formula to return the value from the second column (2).

Keep in mind that the col_index_num argument of VLOOKUP refers to the column number of the table_array, not the column number of the sheet.

If you want to return the value from column 2 of the table_array, then you need to reference at least 2 columns:

VLOOKUP(A3, Products!

B:C, 2, FALSE)That formula says that A3 is the value to be found in Products Column B and the corresponding value from Products Column C should be returned.

If you are simply trying to see if A3 is found in Products Column B, then your col_index_num should be 1:

VLOOKUP(A3, Products!

B:B, 1, FALSE)Does that information help resolve whatever your problem is?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you very much for the replies. I got the equation to work with the cells that has just numbers.

Today, I got the same excel sheet but now I have letters as well.Ex:

TAB 1: 7441XL

TAB 2: AZT7441XLIf I use the same =value formula, it returns a #value!

Would there be any way to match the numbers in TAB 1 with TAB 2 without me having to use the =value(right(A3,4)) equation in TAB 2?

FYI: This excel thing is not my profession and I'm no excel genius. I'm a marketing major and I'm just somewhat familiar with excel through my usage over the years personally.

Anyways, I appreciate all the help.

Thank you.

Try this, if your data looks like this: Sheet 2:

A B 1) AZT7441XL 20 2) AZT7442XL 30 3) AZT7443XL 40 4) AZT7444XL 50 5) AZT7445XL 60 6) AZT7446XL 70 7) AZT7447XL 80 8) AZT7448XL 90 9) AZT7449XL 100 10) AZT7450XL 110 11) AZT7451XL 120On Sheet 1 cell A1 enter your search string: 7445XL

On Sheet 1 cell B1 enter the formula:

=SUMPRODUCT(--(ISNUMBER(SEARCH("*"&A1&"*",Sheet2!A1:A11))),(Sheet2!B1:B11))This will return the contents of the corresponding cell in Sheet 2 Column B.

So, using the above search string it should return 60.

MIKE

Awesome!

That works like a charm!Thank you.

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History