VLOOKUP for merged cells

**Spreadsheet** · 05-09-2007, 01:29 PM

Here's my VLOOKUP problem:

The column that my VLOOKUP formula is finding the lookup_value in contains merged cells. When I ask for a value from a col_index_num that doesn't contain merged cells, the formula returns only the first value. How can I get the formula to return all of the corresponding values?

For example, lets say my lookup_value can be found in A2 where A2 is actually A2:A4 merged. I want values returned from column B which are not merged. The returned value is from B2, but I want B2, B3, and B4 returned because those are the three cells that correspond to the merged A2.

Any help is appreciated.

**Stuart Farr** · 05-09-2007, 05:52 PM

VLOOKUP() will onlyreturn the first value it finds that matches. Why not use SUMIF() instead?

**Spreadsheet** · 05-10-2007, 03:58 PM

My values are not numeric. I want to be able to return each of the three corresponding values. What I do with them after I have them is a trivial problem. Any ideas anybody? Thanks.

**Thanos** · 05-10-2007, 04:50 PM

When you clicked on the merged cells your formula return a range of cells. For example, =vlookup(A2:B6,table1,2,0), the lookup range of vlookup is the merged cells of A2:B6.

Excel will return a #VALUE statement in this instance. To correct the problem have the look up cell only be A2 or the first cell in your merged cells.

Therefore the new example would like =vlookup(A2,table1,2,0) and that will give you a your looked up value from table1.

**Spreadsheet** · 05-11-2007, 01:47 PM

Thanos, I was already using a formula similar to your example. That's not the problem. Does anybody out there know what to do about this issue?

My lookup value for a VLOOKUP formula will be found in a cell that is merged (let's say 3 cells for example). I want to know how I can choose which of the three corresponding values from the col_index_num the formula will return.

Any help would be appreciated.

**NBVC** · 05-11-2007, 02:11 PM

Try something like:

=INDEX($B$2:$B$100,MATCH($X$1,$A$2:$A$100,0)+ROW($A1)-1)

Where A2:B100 contains the table and the value to lookup is in X1.

copy the formula down necessary number of rows to get all info

**aidda** · 05-27-2013, 05:43 PM

I have same problem but I dont understand what is X1 value?

**FDibbins** · 05-27-2013, 05:48 PM

Welcome to the Forum, aidda

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

the value to lookup is in X1.