comparing values and returning different column

**JustMe602** · 05-31-2007, 06:51 PM

Okay so I need some help to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.

HTML Code:

**BigBas** · 05-31-2007, 06:57 PM

How about:

=VLOOKUP(B2,SHEET2!A1:A100,2,FALSE)

Let me know if that works

**JustMe602** · 05-31-2007, 07:15 PM

Due to the values needing to be in ascending order I do believe that is the reason that the Vlookup function will not work in the scenero. Both Sheet 1 & Sheet 2 are not in ascending order based off the information in Column B in Sheet 1 and Column A in Sheet 2.

**BigBas** · 05-31-2007, 10:22 PM

It is possible that your lookup range and lookup array are not the same format. If you post a sample of your workbook, it will be easier to specify the problem.

**tuph** · 06-01-2007, 01:34 AM

Originally Posted by JustMe602

Okay so I need some help to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.

HTML Code:

The data in sheet1 column B (all commencing with "9") does not match any of the data in sheet2 column A ( all commencing with "5"). I assume these are phone numbers, so to find a match you need to eliminate the area codes from the formula.

**JustMe602** · 06-01-2007, 10:23 AM

In the example of data that I initially supplied the data in sheet2 did not have any values that matched sheet1. I upated the example but still need help with the formula. I don't want to get rid of the the first 3 digits.

The data in both sheets have the same formating. So that shouldn't be the problem.

I almost have it in the initial formula, except it only returns column 2 in sheet2 when the information matches the first record in the sheet2.

Thanks the input of information thus far from everyone.

**BigBas** · 06-01-2007, 10:27 AM

VLOOKUP is definitely the function that you need. It's just a matter of applying it.

What exactly is it doing incorrectly? Are your range set correctly? Did you absolute (use the $ sign) the necessary ranges? (It should look like: =VLOOKUP(B2,SHEET2!$A$1:$A$100,2,FALSE). )

Other than that, I would again recommend posting a sample of your spreadsheet (use dummy data if your info is confidential).

**JustMe602** · 06-01-2007, 10:36 AM

I get the #ref! error when I use the Vlookup function as outlined in the previous post. I have attached an example of the data.

**BigBas** · 06-01-2007, 11:06 AM

I see the error, and I initially advised you incorrectly.

Your lookup range has to encompass all of the data you are looking in. You have:

=VLOOKUP(B2,Sheet2!$A$1:$A$2000,2,FALSE)

It should be

=VLOOKUP(B2,Sheet2!$A$1:$B$2000,2,FALSE)

Notice the lookup range has changed from A1:A1000 to A1:B1000

**Mark@Work** · 06-01-2007, 11:09 AM

Try VLOOKUP again but with this small modification

=VLOOKUP(B2,SHEET2!$A$1:$B$100,2,FALSE).

The previous example was trying to look in the second column of a single column array!
Hence the #REF error.

Mark.