Solved If, ISNA, Vlookup question

July 8, 2011 at 17:35:59
Specs: Windows Vista
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 AZB1005

What 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.


See More: If, ISNA, Vlookup question

Report •


✔ Best Answer
July 11, 2011 at 14:25:38
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	120

On 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

http://www.skeptic.com/



#1
July 8, 2011 at 19:11:44
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

http://www.skeptic.com/


Report •

#2
July 8, 2011 at 19:20:32
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.


Report •

#3
July 11, 2011 at 09:14:08
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: AZT7441XL

If 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.


Report •

Related Solutions

#4
July 11, 2011 at 14:25:38
✔ 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	120

On 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

http://www.skeptic.com/


Report •

#5
July 11, 2011 at 16:14:12
Awesome!
That works like a charm!

Thank you.


Report •

Ask Question