VLookup Newbie Question

[Marty1111]

Hello All!

A newbie here. I appreciate any help. I am actually quite good in a # of areas - but not in VLookups. I basically have two tabs with similar lists of customers and misc info. One list has addresses and the other doesn't. Each customer has a customer # reflected on both tabbed page. My formula wld basically be: if customer # on tab 1 ='s customer # on tab 2 - take address from tab 1 and place in customer's row on tab 2.

Thanks 4 anyone helping me with this. I have already entitled the data in tab 1 (where the address is) "Table1."

A sample I was using to try to duplicate is as follows: (It however has a little more info in it which is confusing me) -
=IF(VLOOKUP(D3,Table1,1)=D3,IF(VLOOKUP(D3,Table1,2)=0,"DNP",VLOOKUP(D3,Table1,2)&IF(LEN(VLOOKUP(D3,Table1,2))=7,"","-"&VLOOKUP(C3,table2,2))),"Missing GL Number")

The CUST# is in Column B in Table 1 (1st Tab) and in Column E in the target table. The address is in column I. I realize I will be putting the VLookup formula in column I of the target table.

Also, if it is fairly easy if someone cld interpret the above sample - I'd be very grateful.

Again - thank you 4 taking the time to help. I really appreciate it. Marty

 

[Misca]

The long example formula is first checking if the D3 can be found from the Table1. If not, the it'll return the "Missing GL Number". If a match is found then it checks, if the second cell next to the D3 match is empty (or zero) and returns "DNP" if so. If there's something in the next cell then it returns that value and checks how many characters there are in it. If it's 7 characters long, the it'll add a space. If it's not 7 characters long it'll return "-" and the matching value for C3 from the 2nd column in Table2.

Since none of the VLOOKUPs are using the FALSE parameter the searched ranges must be arranged in ascending order and they will return the closest smaller match if no exact matches are found (if you're looking for the match to 10 and there list goes "3, 5, 9, 11" the VLOOKUPs return the match for number 9 since 10 is missing - and if 10 is after 11 the lookups don't look that far).

The lookup formula to return the matching customer address would be something like

=VLOOKUP(B2,Table1,4,false)

This returns the exact match for the B2 from the 4th column in the range Table1. Correct the B2 and change the 4 into the right number since I might have misunderstood your description.

Hope this helps.

 

[Marty1111]

Thank you so much 4 the time you have taken on this topic! I greatly appreciate it. I see your answer above. It basically says lookup the value in B2 of table 1... I believe. But what does the 4 and false mean? 'Sorry to be so slow on this. How does one say if Column A on Table 1 ='s Column G on Table 2, then enter the data from Column J on Table 1 in this field.

Thanks! Marty

 

[Misca]

The 4 means that it'll return the value from the 4th column of the Table1 range and the FALSE stands for exact match (=the values have to be identical, otherwise it'll return error. ). If you change it to TRUE or leave it out, VLOOKUP will return the closest (smaller) match: If you're looking for number 10 and the list goes something like "2, 4, 7, 12" the formula leaves the match for number 7 when 10 is not found where as FALSE returns an error saying it can't find the value it's looking for.

The "how does one say" part is a bit trickier. I could answer it easily if I saw the workbook but without it I'm a bit lost: usually you're just comparing one cell and see if there's a match or not but you're asking to compare columns. That can be done with array functions but I don't think that's really what you're after. The problem is that I don't understand the ranges by their names: Give me the exact ranges (ie "Table1=A2:D8 and Table2=J12:Y:782") and I may be able to understand your question & answer it.

 

[Marty1111]

Thanks Misca. For simplicity sake, let's say Table 1 has Cust# in range A2:A500 and Table 2 has Cust# A2:500. The addresses are in C2:C500 (Table 1) and the blank column that I want to populate with the addresses from Table 1 in Table 2 is G2:G500. I'm basically saying if the Cust#s match between each table, enter the corresponding address into Table 2, column G.

I've looked at a # of VLOOKUPS and this appears to be pretty straight forward. For instance I have seen something like: the VLOOKUP table has a list of different political parties in column b (2) and their codes in column a (1). If a specific code is in the data in the target table - then get the corresponding political party name and enter it 'here' (in the target table).

Also, thanks for the help with the '4' and the false. I really shld have already had that down... but! I am beginning to pull this together with your help. I do appreciate it. Maybe this further clarity will help get what's in my mind into yours... so we can get this one off my plate.

 

[Misca]

Okay, I should've guessed that the tables are on different sheets. Here's a quick & easy solution:

Go to your Table1 (= the one where the addresses are) and select the whole table (=A2:C500). When you have done this, type something like CustData to the Address Bar (= the small bar right next to the formula bar that says the cell address). This names the range you've just selected as CustData. Naming the range saves us from a couple little problems we'd have deal with otherwise: You don't have to use the sheetnames or the $-signs in the formula & even the formula itself looks prettier & is easier to read afterwards.

Okay, you've now named the range CustData so go to your Table2 and type the following formula to cell G2:

=VLOOKUP(A2,CustData,3,FALSE)

If you want to make your worksheet prettier (=avoid error message) you can modify this formula with a simple IF formula:

=IF(ISERROR(VLOOKUP(A2,CustData,3,FALSE)),"Address Unknown",VLOOKUP(A2,CustData,3,FALSE))

This formula checks for errors and changes them into "Address Unknown".

Either way, the formula should now return the address for the customer number in A2. If the column F has data in it all the way down to row 500 you can now double click the small black dot on the lower right corner of G2 (can be seen when the cell is selected) and the formula should copy down automatically. If there are blank cells in column F, the copy only works 'till the first blank cell at a time OR you can first hide the columns B:F and then do this trick and it should work all the way down to row 500 with just one double click.

 

[Marty1111]

I am **so** glad you hung in there with me! I did know about naming a range - and can see that it wld keep things cleaner. Thx 4 the reminder. And! I appreciate you throwing in the 'address unknown' aspect - that does make things much neater. So! I tried it and it works.

For this to work does the Customer # have to be in the same column on both worksheets? I am trying to 'read' the code and it seems to be very abbreviated.

To me it reads like "look up in the VLOOKUP table (CustData) check the contents of Column 1 of this ws, take the exact contents of Column 3 (from the VLOOKUP table) and put them here" I don't see where it says "check that Column 1 of this ws matches Column 1 in the VLOOKUP table." I'm basically wondering how Excel knows to check column 1 of the VLOOKUP table against Column 1 of the target ws.

Otherwise! This does it. Thanks so much. I hope to be of assistance to you and/or the group someday myself. Marty

 

[Misca]

You don't need that "check that Column 1 of this ws matches Column 1 in the VLOOKUP table" part. The VLOOKUP does that for you: If there's no match you'll get the error (/"Address Unknown"). However, the VLOOKUP doesn't check that you have all the same customer id's in both sheets. If you want to do this, type a COUNTIF formula in one of the empty columns in Table1 (=the one where you're getting the addresses from) and make sure the formula returns 1 for every customer number.

The formula should be something like "=COUNTIF(your sheetname!A:A,A2)"

The customer number doesn't have to be in the same column in both sheets but it does have to be on the leftmost column of your search range (=the range where you're getting the addresses from). The VLOOKUP only looks for the left column and can only return values from the columns on the right. There are other search functions that can return values from both sides but they're a bit harder to use so they're not nearly as common as the VLOOKUPs and the HLOOKUPs.

And as far as your reading of the formula goes you're reading it about right. I wouldn't use those exact words but the idea is the same. I'm reading the function something like "A2 is the value we're looking for from the leftmost column in the CustData range and when there's a match the formula returns the value from the 3rd column (= 2 columns to the right)."

 

[Marty1111]

Boy, explaining the left column aspect is very helpful. I am glad I asked the question - because I did not realize that the column's proximity to the other column mattered. Thank you again 4 all your help! This has been a big help. Misca, you are a lifesaver! Marty