vlookup requires a one by one manual copy per celll to work

**Loreto** · 01-26-2012, 11:28 AM

Hello all,

I have two HUGE spreadsheet that I use for financial analysis. I have reduced the size in the attached versions to clearly identify my issue.

In the spreadsheet "allotment test", in order for the formula to work, I have to copy the complete cell in column M to the corresponding cell in Column A of the "office test".

This allows the formula to work. However, a copy of the format does not change the other cells. How can I easily fix this (otherwise, I would have to update 600 cells one by one).

Thanks,

Loretooffice test.xlsx allotment test.xlsx

**NBVC** · 01-26-2012, 11:57 AM

Your dilemma isn't really clear.

Which formula is not working? Can you elaborate a bit?

**NBVC** · 01-26-2012, 11:58 AM

Your dilemma isn't really clear.

Which formula is not working? Can you elaborate a bit?

**Loreto** · 01-26-2012, 01:22 PM

To clarify, in the spreadsheet "Allotment Test", my objective is for the formula vlookup to work with columns B and D.

So, what I have done, since the formula isn't working, is copy the cell in column M (activity number) on this sheet (allotment test) and paste it on top of the same number on "Office Test" in column A.

At this point, the formula works for that specific activity code. However, the real spreadsheet is 600 rows long. I cannot copy-paste all day. And unfortunately the copy-format function will not work. Any suggestions?

I hope that this clarifies my issue.

Thanks for looking.

Loreto

**NBVC** · 01-26-2012, 02:21 PM

In column A of the office test.xlsx workbook, you have trailing space (ASCII char 160) at end of each word... you should remove them.

If all the codes are the same length, you can use Data|Text to Columns, select Fixed width, click Next, click in the Viewing Pane right after the codes, click Next,

Select second column in viewing pane, then select do not import column(skip) and click Finish.

alternatively, if all of them are always going to have that trailing invisible char, change Vlookup to:

=VLOOKUP(M11&CHAR(160),'[office test.xlsx]RLC-SLC-SLM'!$A:$IV,4,FALSE)

**NBVC** · 01-26-2012, 02:22 PM

In column A of the office test.xlsx workbook, you have trailing space (ASCII char 160) at end of each word... you should remove them.

If all the codes are the same length, you can use Data|Text to Columns, select Fixed width, click Next, click in the Viewing Pane right after the codes, click Next,

Select second column in viewing pane, then select do not import column(skip) and click Finish.

alternatively, if all of them are always going to have that trailing invisible char, change Vlookup to:

=VLOOKUP(M11&CHAR(160),'[office test.xlsx]RLC-SLC-SLM'!$A:$IV,4,FALSE)