formula not yielding value from vlookup

**vislavti** · 10-30-2011, 01:08 PM

All -

I had posted a query and got that resolved with help of excellent members of this group.

http://www.excelforum.com/excel-prog...-new-post.html

I implemented the solution suggested but below piece of code is not putting the value out of vlookup. The column is populated with "#VALUE!". I have to press CTRL+SHIFT+ENTER in order to have the results populated.

With Range("B2")
    .FormulaR1C1 = "=VLOOKUP(lower(RC[-1]),trim(lower('Team Detail'!R2C2:R200C5)),2,0)": .Resize(k).FillDown
End With

Can anyone suggest what is the reason and what could be done to resolve it?

**Leith Ross** · 10-30-2011, 01:56 PM

Hello vislavti,

The #VALUE error indicates that VLOOKUP did not find a match. You should check that your cell references are correct. Here is what the references look like in A1 style.

=VLOOKUP(LOWER(A2),TRIM(LOWER('Team Detail'!$B$2:$E$200)),2,0)

**vislavti** · 10-31-2011, 11:10 AM

Thank you for your reply. Could you please tell me why pressing CTR+SHIFT+ENTER brings result?

**Leith Ross** · 10-31-2011, 11:23 AM

Hello vislavti,

A formula is entered with Ctrl+Shift+Enter when the operation is applied to multiple cells simultaneously.Formulas that operate on more than one cell are know as array formulas. You must enter array formulas with Ctrl+Shift+Enter and drag them down the range to see the individual results. If you don't then only the first result is returned.