Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.
SheetB
| A | B | C |
1 | ExampleID | Name Format | Last Name |
2 | Example0001 | Business | |
3 | Example0002 | Business | |
4 | Example0003 | Family | |
5 | Example0004 | Individual | Thompson |
6 | Example0005 | Individual | Blakely |
7 | Example0006 | Individual | Smith |
8 | Example0006 | Individual | Smith |
9 | Example0006 | Individual | Smith |
<colgroup><col style="width:30px; "><col style="width:115px;"><col style="width:108px;"><col style="width:93px;"></colgroup><tbody>
</tbody>
Spreadsheet Formulas |
Cell | Formula | C2 | {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A2&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} | C3 | {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A3&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} | C4 | {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A4&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} | C5 | {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A5&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} | C6 | {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A6&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} | C7 | {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A7&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} | C8 | {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A8&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} | C9 | {=IFERROR(INDEX(SheetA!$F$2:$F$7,MATCH(SheetB!$A9&"/"&"Individual",SheetA!$A$2:$A$7&"/"&SheetA!$B$2:$B$7,0)),"")} |
<tbody>
</tbody> |
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER! |
<tbody>
</tbody>
SheetA
| A | B | C | D | E | F | G |
1 | ExampleID | Name Format | Title | First Name | Middle | Last Name | Suffix |
2 | Example0001 | Business | Mr | John | | Jones | OBE |
3 | Example0002 | Business | | J | | Robson | |
4 | Example0003 | Family | | | | | |
5 | Example0004 | Individual | Dr | John | T | Thompson | |
6 | Example0005 | Individual | Mr & Mrs | John & Mary | | Blakely | |
7 | Example0006 | Individual | Prof. | Tom | M | Smith | |
<colgroup><col style="width:30px; "><col style="width:110px;"><col style="width:105px;"><col style="width:74px;"><col style="width:93px;"><col style="width:51px;"><col style="width:85px;"><col style="width:43px;"></colgroup><tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4