• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLookup problem

W

wnorrick

Member
I have two worksheets both of which have last name and first name of residents. one worksheet has the delinquency/prepaid amount and I need to do a lookup by name and bring the delinquency/prepaid amount into the the second worksheet. The lastname is in the first column in both worksheets. Both sheets are in alpha order by last name. the formula i have entered on the first worksheet is:

=VLOOKUP($A2,B_EOM!$A$10:$A$227,5,FALSE) A2 is the last name on the first sheet, A10:A227 in the second worksheet (B_EOM) is also the last name. The delinquency/prepaid amount is in the 5th position from the last name in the B_EOM worksheet. I have very similar formula on other worksheets with success so I am confused as to why it isn't working. I am mostly getting #REF on most and and few #NA. on the #REF the names are the same on both sheets. Help please. in addition if there is a way to look not only at last name but last name and first name that would be great. Do I need to combine them with a comma or can I do it with them in their own columns?
 
Your vlookup is returning #REF because you are using only column for lookup area, where you want information from 5th column. Make this change and it should work:


=VLOOKUP($A2,B_EOM!$A$10:$E$227,5,FALSE)


If you want to use both first name and last name in the lookup, use this array formula:


=INDEX(B_EOM!$E$10:$E$227, MATCH($A2&$B2, B_EOM!$A$10:$A$227&B_EOM!$B$10:$B$227, 0))


This is an array formula, so use CTRL+Shift+Enter once you pasted it.


To know more about this technique, refer to http://chandoo.org/wp/2010/11/02/multi-condition-lookup/
 
Thank you so much. I obviously need to go back through your classes:) In fact I am anxiously awaiting the roll out of Vitamin XL.
 
You must log in or register to reply here.
Back
Top