Excel Conditional Cell

Microsoft Excel: mac 2008 (mac)
July 28, 2009 at 15:43:51
Specs: 10.5 Mac OS
I have two excel work books, one has two columns. First column contains a part number and the second contain the description for the part number to the right of it. In the second workbook i have a list of parts. that i need to match descriptions to, using the data in the first workbook. Is this possible to do?

See More: Excel Conditional Cell

Report •

July 28, 2009 at 16:28:35
Is the description in the second column the same as the "list of parts" in the other workbook?

If you have 3 columns that don't have anything in common, it would really tough to match things up.

Perhaps if you provided some examples of your data it would help us help you.

Report •

July 29, 2009 at 07:53:14
Column 1 Column 2 Column 3
1 5 Red
2 4 Blue
3 3 Green
4 2 Orange
5 1 Black

I need to match Column 3 to column 1. Column two and 3 are in the same workbook. So i would like a formula or macro to find the number one in the second column then put the coresponding color in the column next to Column one.

Report •

July 29, 2009 at 07:57:36
Column 1 Column 2 Column 3

That is a better look

Report •

Related Solutions

July 29, 2009 at 08:15:41
Have you tried VLOOKUP? Works for me...

=VLOOKUP(A1,'C:\Documents and Settings\user_name\Desktop\[Book2.xls]Sheet1'!$B$1:$C$5,2,0)

You need to make sure you have the full path name in the formula. This is most easily done by saving both files first so that when you select the lookup_array as you build the formula, it will automagically insert the path after the workbook is closed.

Report •

July 30, 2009 at 12:26:26
I used VLOOKUP but it is not always linking the correct value. It will give me a value but it is not matched correctly to the part number. If i put in False for exact match. It does not give me any value just states N/A, and only works on the first cell and nothing past it.

Report •

July 30, 2009 at 18:54:13
I used your example data, putting 1 - 5 in A1:A5 of Book1.xls and 5 - 1 and the colors in B1:C5 of Book2.xls.

The formula I posted is placed in B1 of Book1 and dragged down to B5. It returns the the correct color every time. I can change the colors in Book2, save and close it, and when I open Book1 it updates the colors to match the changes.

Since the only thing I have to work with is your example data and you didn't post the formula that you tried, I can't offer any suggestions as to why your VLOOKUP isn't working.

Report •

July 31, 2009 at 10:22:30
I have put all the data i need in three columns. Similar to what i showed you at the first time. =VLOOKUP(A1,B1:C20000,2) that is what i am using. What will happen, is the first row will give me the correct answer but after that it will give me the wrong return. There are a few cells in the A column that do not have a value in the table_array. Not sure if that would affect the return values.

Report •

July 31, 2009 at 11:47:07
Once again without seeing your actual workbook, I am limited in what suggestions I can offer.

First, if you are using the exact formula that you have posted and are dragging it down, let say in Column D, then your lookup_array is changing in each cell.

You need to use Absolute Referencing to keep your lookup_array constant. Highlight B1:C20000 in your first formula and hit F4. You should see this:


Now when you drag it down, the $B$1:$C$20000 will not change.

Second, since you have omitted the range_lookup argument, it defaults to TRUE meaning that it will try to find an approximate match if it can't find an exact match. Read the VLOOKUP Help file in Excel to see how it handles what it calls an "approximate match".

If you want it to find only exact matches, use this:


Finally, if you don't want to see the #N/A error should one legally occur, you can use something like this:

"No Match Found",VLOOKUP(A1,$B$1:$C$20000,2,0))

I hope something here helps.

Report •

July 31, 2009 at 12:08:31
I do have a sample worksheet i can give you so you can look at it. I am sure i have not been too clear. So if you have a email or something, I can attach the sample. That is if you have time. Thanks for all your help.


Report •

July 31, 2009 at 12:19:18
I've sent you an email. Reply with the spreadsheet and I'll see what I can do.

Please do not share the email address with anyone.

Report •

August 16, 2009 at 04:11:44
hi every 1 i have a question, i tried vlookup, but didn't work;
please some 1 tell me i would like to give a cell a name(abriviation), so whenever i type the abbriviation automaticaly give me the full name in very same cell, e.g;
cel 1 cel2
r ranger
b bahter

in another cell different sheet whenever i type r automaticaly give me ranger in same cell.

thanks in advance.

Report •

August 16, 2009 at 10:58:45
Please post your question in it's own thread.

Report •

Ask Question