Vlookup?

[elliottcor]

I知 not an expert w/ excel by any means and I知 trying to figure out a solution to a problem I have. I知 guessing this is probably a slam dunk for someone who is a regular user. Here is the situation. I have one tab that lists account numbers in one column and an identifier (type of account) listed in a second column. Then I have a second tab with the same account numbers but in many instances the account numbers are listed more than once. I want to be able to carry over that identifier in the first tab to the tab that lists the account numbers multiple times match it up. Does that make sense?

[ConneXionLost]

On the second sheet, something like:

Please Login or Register  to view this content.

Where 'Sheet1'$A$1:$B$100 is your table on the first sheet.

[elliottcor]

I must be doing something wrong. not working. here is what might be a better explaination. I'm trying ot carry over the identifier to Sheet 2


Sheet 1

Account Identifier
123456 1
789652 3
564135 2
562723 6


Sheet 2

Account Identifier
123456
123456
123456
789652
564135
564135
562723

[FDibbins]

Check to see if either set of numbers have a small green triangle in the top left corner. It could be that 1 set is text that just looks like a number. Where are the 2 sets coming from?

[Pete_UK]

It might be that your account numbers in one sheet are proper numbers, but in the other sheet they are text values that look like numbers. Try either of these amendments to the formula that ConneXionLost gave you:

=VLOOKUP(A2&"",'Sheet1'$A$1:$B$100,2,0)

or:

=VLOOKUP(A2+0,'Sheet1'$A$1:$B$100,2,0)

Hope this helps.

Pete

[elliottcor]

this doesn't appear to match up the identifier to the account number.

[FDibbins]

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like? That way, we can stop guessing

[ConneXionLost]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[elliottcor]

trying to pull the identifier in sheet 1 to sheet 2.

[MarvinP]

Hi Elliott and welcome to the forum,

Now that you've learned how to attach a sample file, see if my attached answers your question.

[elliottcor]

thank you. the formula works in theory on the sample but on my spreadsheet something isn't right. I have about 5,000 rows. The identifiers are not matching up with the account numbers and they just stop about halfway down the spreadsheet. for example on sheet 2 for those that populated the identifier its not correct. the remaining rows I get the N/A.

[PeteABC123]

Can you post a workbook that contains errors? Sanitize as necessary; before uploading.

[ConneXionLost]

Sounds like you haven't used absolute references. Ensure you have the "$" signs as previously used.

[elliottcor]

my thought is the formula that was suggested is not taking into consideration the duplicate accounts for some reason. the number of accounts that were populated equals the number of accounts in sheet 1. after that it falls off the rails. sheet 2 has duplicate account numbers that need to be populated w/ the identifer.

[FDibbins]

1. vlookup() finds the 1st matching record (or the 1 below where the match would have been, then stops looking
2. did you extend the ranges down far enough?

[elliottcor]

this is my formula i'm using:

=VLOOKUP(A2,Sheet1!A2:B1594,2,0)

[elliottcor]

definitely expanded the range all the way down. is vlookup to simple of a function for what i'm trying to do?

[Pete_UK]

You should use $ symbols in front of those row references, so they don't change when you copy the formula down. Alternatively, you could do this:

=VLOOKUP(A2,Sheet1!A:B,2,0)

then copy down.

Hope this helps.

Pete

[athompy]

Have you considered trying an IF function? That is what I would do for this situation, maybe VLOOKUP is a better approach. But in sheet 2 column B I would use =IF(A2=Sheet1!$A$2,Sheet1!$B$2,IF(A2=Sheet1!$A$3,Sheet1!$B$3,IF(A2=Sheet1!$A$4,Sheet1!$B$4,IF(A2=Sheet1!$A$5,Sheet1!$B$5,""))))
Maybe this is just the long-hand of what VLOOKUP would do but I like being able to see what all is going on.

[FDibbins]

@ athompy, using IF's like that for a limited number of options is fine, and like you say, it can help show what you are doing. But beyond 4 or 4 options, it can become cumbersome and overly-complex. Functions like vlookup() are designed to andle these large volumes of criteria

@ elliottcor, sometimes, making up dummy examples doesnt quite do your data justice, I think this is 1 of those times. Can you upload some data that is closer to what your real data looks like?

[elliottcor]

This was it Pete. Thx! Solved!