Excel VLOOKUP Help 2

[Texanite]

Need vlookup help on the following problem. I have a spreadsheet with a tab called "Rollupsort" that has a column A & B through row 415. I have another spreadsheet with a name tab of "Account Analysis". I am trying to find a match in column A in "Rollupsort" with a Match in Column B in "Account Analysis" and put what is in column B in "Rollupsort" in column A of the "Account Analysis" spreadsheet. Every row in the B column of the "Account Analysis" spreadsheet should have a match within column A of the "Rollupsort" Spreadsheet I've tried several things with VLOOKUP but it's not working. Hope this is not clear as mud. If you have questions, let me know. But you programmers please be patient with me... I'm an 'ole lady!

Thanks,
Texanite

 

[Zathras]

VLOOKUP is indeed the way to go. Are you trying to type the formula "by hand" instead of using point and click? It can be a little tricky getting the syntax right when working across multiple sheets.

For example, cell [blue] A2 [/color] would have:
[blue]

  =VLOOKUP(B2,Rollupsort!$A$1:$B$415,2,0)

[/color]

 

[Texanite]

That is indeed what I'm doing. However, tried your formula and am getting a #N/A result. There should be a match for everything within the Rollupsort table.

Thanks,
Texanite

 

[Zathras]

That can happen when you have text that looks like numbers and you are trying to look up with actual numbers (or vice versa). You must lookup text with text and numbers with numbers.

Can you provide some data samples (or make some up)?

What version of Excel are you using? The conversion from textual numbers to real numbers can be done in a number of ways. Some later versions of Excel have tricks that aren't available in '97 or 2000.

 

[sawedoff]

Texanite,
One of the most common reasons for the #NA when you know the data is there would be that your target (Rollupsort) is not sorted, or the data in one is numeric, while the other is text. Check both of those possibilities.

Sawedoff

Give me ambiguity or give me something else.

 

[Texanite]

OK, that was the problem for the most part, the Rollup sheet was not sorted. I was also trying to compare a character with a value so fixed that and sorted the Rollup sheet and it works on all 20,000 rows EXCEPT the first row! Why won't it work ont he first row?

Texanite

 

[sawedoff]

When Excel does a lookup, it basically cuts the target in half, do determine whether to look in the first hals or last half, then cuts the halves, etc. A lot of the time, you still come out with the right answer, but sometimes you run into a "gotcha!". Sounds like you got the "gotcha"sooner than later. Glad it helped.

Sawedoff

Give me ambiguity or give me something else.

 

[KenWright]

The data does not need to be sorted as long as you use the optional 4th argument in the VLOOKUP rfunction and make it 0 or FALSE, eg as in Zathras' example formula. Omitting this argument is the same as having it set to TRUE which will give you the closest match it comes across first.

As for the first row, do you by any chance have any leading or trailing spaces in the data that would cause it not to match.

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------