CODEWITHSUNDEEP
excelvlookup
Aira
Aira

Reputation: 179

How to create result table with vlookup from merged cells

I have this data table and i want another result table. when I write name of state ,result table can show all of company with data1,data2 and data3.I trying use vlookup but because there are merged cells the formula just show first row. how can I fix problem?

enter image description here

Upvotes: 1

Views: 38522

Answers (1)

chuff
chuff

Reputation: 5866

If I'm understanding correctly, you want to set up a lookup range so that when you enter a particular state, you can see the data for all the companies that have data in that state. Here is one way to do that.

The first thing you would need to do is set up three columns to the left of the original table:

  • The first column holds the name of the state associated with each row of data
  • The second is an index that counts off the number of data rows in each state
  • The third combines the first two columns to produce a unique key value for each row in the table.

All the values in these three columns can be assigned by formula. The picture below shows the formulas for the first row of cells A9:C9, which are then copied down through row 27.

columns for lookup key

The next step is to lay out the new table, which is in cells Q8:U27 in my example.

There are several thing to note about the setup. First, the state that will be displayed is entered in cell Q9, which I've highlighted in yellow. To the left of the table, in column P, I've entered item numbers from 1 to 19, which will be needed to construct the key values for the lookups. The lookup formulas themselves are in cells R9:U27; in the picture, the formulas for the first row (R9:U9) are shown (they are then copied down through row 27).

new table

It's worth taking a moment to look more closely at one of the lookups. Here is the formula for the first company name in cell `R9'.

  =IFERROR(VLOOKUP($Q$9&$P9,$C$9:$N$27,4,0),"")

Looking at each of the arguments of the VLOOKUP in turn, $Q$9&$P9 concatenates the state name in cell Q9 with the item number (1 in this case), yielding the lookup value 'California1'. The lookup table is defined as the range $C$9:$N$27 - column C of that range is what the lookup value is matched against. The third argument is the column from which to return a value if the lookup is a match. The number 4 here corresponds with the company name column of the original table. Finally, the last argument is 0 (or equivalently, FALSE) indicating an exact match is required.

Finally, the VLOOKUP function is wrapped inside IFERROR. This catches the #N/A that would otherwise be returned when no match is found, replacing it with an empty string ("").

Upvotes: 4

Related Questions