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.

Please Login or Register  to view this content.

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.