Lookup and join multiple lookup values based on a "X" flag

joesalas

New Member
Joined
Jun 16, 2005
Messages
27
Would you be able to assist me with a lookup function that joins multiple values based on the value of a "X" flag?

Within a row, gather all cells that equal "X" and join all table header values within a single cell.



Here is a html example:

<Html>
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2">
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Item Number</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">North</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">South</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">West</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Return Value In this Column</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">1</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">North, South</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">3</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">4</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">South, East</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">5</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East, West</font></td>
</tr>
</table>
</html>

Thanks for your help!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

tybaltlives

Active Member
Joined
Nov 6, 2006
Messages
414
I dont have the HTML paster so here goes:

Put your columns of headers in line 1 in separate cells (H1, H2, H3, H4, H5).

Right below this your rows of "x"'s start. Leave a cell blank if you dont want the header. Each row may have different combinations of x's, of course.

Now, right below your rows of x's, put in the following formula: =IF(E2="x",E$1,""). Row 2 is the first row with any x's in it.
This can be copied horizonally and vertically. It returns the header line any time an x is in a cell within a row.

Finally, put the following formula at the bottom (it also can be copied horizonally and vertically: =E6&F6&G6&H6&I6. Row 6 in my example is the first row with the pre-concatenated headers.

This last formula concatenates every cell in the row, but the cells without an x are null characters so you dont see them.
 

joesalas

New Member
Joined
Jun 16, 2005
Messages
27
Thanks for your response
however, that process is a bit manual and either provides too many commas or not enough to seperate the values of the cells.
I'd have to do something like this
(H2&","&I2&","&J2&","&K2) with the result of
North,South,,

<html>
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2">
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Item Number</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">North</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">South</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">West</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Return Value In this Column</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Item Number</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">North</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">South</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">West</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Return Value In this Column</font></td>
<td bgcolor="#00F406" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">RESULT</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">1</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">North, South</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">1</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">North</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">South</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">North, South</font></td>
<td bgcolor="#00F406" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">North,South,,</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East</font></td>
<td bgcolor="#00F406" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">3</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">3</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#00F406" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">4</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">South, East</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">4</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">South</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">South, East</font></td>
<td bgcolor="#00F406" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">5</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">x</font></td>
<td bgcolor="#FFFFFF" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East, West</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">5</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">West</font></td>
<td bgcolor="#E2EFDA" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">East, West</font></td>
<td bgcolor="#00F406" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
</table>
</html>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top