I have an Excel spreadsheet (sheet 1)- in one column "C" with account Code numbers in alpha numeric form in subsequent rows but no account name. In another sheet (2) I have full list of account code numbers in one column and against each code the relative account name in another column in subsequent rows. How do I insert the respective account name in sheet 1 against the account code by picking the right name from sheet 2. I think I need to conditional format sheet 1 but I don't know how to. Please help. Thanks

Read up on the VLOOKUP function in the Excel Help files. Although you didn't give us a lot of information related to how your data in Sheet2 is laid out, it sounds like VLOOKUP is what you need.

Conditional Formatting has nothing to do with looking up or retrieving data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have read the VLOOKUP function and tried it out but it appears that I am doing something incorrectly. In my workbook in sheet 1 in Column F I have supplier code numbers from Row 11 to Row 251. In sheet 2 in Column B I have the supplier Code numbers from Row 6 to Row 240 and in Column C I have the supplier name against each code number. I have created a blank column H in sheet 1 and I would like the supplier name to be inserted against each code number in Column H. Instead of writing the actual code number which is alpha numeric text I am quoting the cell (F11) in the first row of my formula assuming VLOOKUP will search for the text that is in F11 in sheet 2. Where am I going wrong as I am getting #N/A or #Ref! as the result in Column H? Sorry for the lengh of this follow up but it is the only way i can explain my problem. Thanks for your time and assistance.

Based on your description, this should work: =VLOOKUP(F11, SHEET2!$B$6:$B$240, 2, 0)

If you are getting #N/A it is because VLOOKUP is not finding a matching value in SHEET2!$B$6:$B$240.

Maybe you've got an extra space or something like that in one of the lists.

Try this test:

Find a matching value for SHEET1!F11 in SHEET2 Column B and note what cell it's in, e.g. SHEET2!B23.

Enter this in any cell =SHEET1!F11 = SHEET2!B23

If there is an exact match, that will return TRUE and VLOOKUP should work. If the values are different in any way, it will return FALSE and VLOOKUP will return #N/A.

If they don't match, you'll have to figure out why.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have figured why I was getting the wrong result. I was not quoting the array_table correctly by not including the column C in the range. Thanks for your help as I never knew Excel had VLOOKUP function. I can make my reports more detailed now. You have been a great help. Thanks once again for your time and assistance.

XEL LEARNER

If you are going to use VLOOKUP, you should also be aware of the ISNA function. There will be times when VLOOKUP will not find what it is looking for but that's OK. Often VLOOKUP is used not just to retrieve data, but also to find out if a given value is in a list. If the value isn't found, VLOOKUP will return #N/A letting you know that the value doesn't exist.

Since #N/A is "ugly", you can use the following formula to return a more meaningful message if a value isn't found. What is does is essentially check for the #N/A error before actually processing the VLOOKUP.

=IF(ISNA(VLOOKUP(F11, Sheet2!$B$6:$B$240, 2, 0)), "Value Not Found", VLOOKUP(F11, Sheet2!$B$6:$B$240, 2, 0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History