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

joesalas

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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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.
 
Upvote 0
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Picture won't paste in cell overlay
Replies
0
Views
62
Cerka
C
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Adding a custom message.
Replies
1
Views
421
Micron
M
Z
  • Question
Sumifs Formula Help 2 Criteria i think
Replies
1
Views
275
zone709
Z
Z
  • Question
Formula to match multiple things so I can get a result to tell me what I am off on.
Replies
1
Views
274
zone709
Z

Forum statistics

Threads
1,214,641
Messages
6,120,688
Members
448,978
Latest member
rrauni

Share this page

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
Back
Top