it's not vlookup so do I need index?

[steviewonderpants]

Hello,

I have two tabs of data that I am using to consolidate data to create a fax template, using 1 tab for the fax, and the second to hold reference data.

Essentially I want to type my fax recipient in column B3, and use this "lookup code" to reference my DATA tab and pull in multiple sets of data.

If I were to use VLOOKUP, I know the formula would be =VLOOKUP($B$3,DATA!$A$1:$G$49,1,FALSE), however the issue I have is that it only returns the very first instance where B3 is found in column A of the data tab. What formula do I need to type in place of the VLOOKUP to pull in all instances of where B3 matches something in column A of the data tab.

Many thanks
SD

 

[jim may]

Your formula:

=VLOOKUP($B$3,DATA!$A$1:$G$49,1,FALSE), Returns what's in your Sheet Data Column A
=VLOOKUP($B$3,DATA!$A$1:$G$49,2,FALSE), Returns what's in your Sheet Data Column B
=VLOOKUP($B$3,DATA!$A$1:$G$49,3,FALSE), Returns what's in your Sheet Data Column C
=VLOOKUP($B$3,DATA!$A$1:$G$49,4,FALSE), Returns what's in your Sheet Data Column D
and so on....

 

[mick0005]

I think this is what you want. This is an array formula, so you will need to execute it with CTRL+SHIFT+ENTER and you can drag it down a bunch of rows (as many as you need to satisify the maximum results you'd expect).

Excel 2010

	B	C
2	Output Criteria:	Results:
3	Test_2	Result_T2.1
4		Result_T2.2
5		Result_T2.3
6		Result_T2.4
7

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C3 {=IF(ROWS(DATA!B$2:B2)>COUNTIF(DATA!$A$1:$A$8,OutputCriteria),"",INDEX(DATA!$B$1:$B$8,SMALL(IF(ISNUMBER(SEARCH($B$3,DATA!$A$1:$A$8)),ROW(DATA!$B$1:$B$8)),ROWS(DATA!B$2:B2)),1))} C4 {=IF(ROWS(DATA!B$2:B3)>COUNTIF(DATA!$A$1:$A$8,OutputCriteria),"",INDEX(DATA!$B$1:$B$8,SMALL(IF(ISNUMBER(SEARCH($B$3,DATA!$A$1:$A$8)),ROW(DATA!$B$1:$B$8)),ROWS(DATA!B$2:B3)),1))} C5 {=IF(ROWS(DATA!B$2:B4)>COUNTIF(DATA!$A$1:$A$8,OutputCriteria),"",INDEX(DATA!$B$1:$B$8,SMALL(IF(ISNUMBER(SEARCH($B$3,DATA!$A$1:$A$8)),ROW(DATA!$B$1:$B$8)),ROWS(DATA!B$2:B4)),1))} C6 {=IF(ROWS(DATA!B$2:B5)>COUNTIF(DATA!$A$1:$A$8,OutputCriteria),"",INDEX(DATA!$B$1:$B$8,SMALL(IF(ISNUMBER(SEARCH($B$3,DATA!$A$1:$A$8)),ROW(DATA!$B$1:$B$8)),ROWS(DATA!B$2:B5)),1))} C7 {=IF(ROWS(DATA!B$2:B6)>COUNTIF(DATA!$A$1:$A$8,OutputCriteria),"",INDEX(DATA!$B$1:$B$8,SMALL(IF(ISNUMBER(SEARCH($B$3,DATA!$A$1:$A$8)),ROW(DATA!$B$1:$B$8)),ROWS(DATA!B$2:B6)),1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To OutputCriteria =Sheet1!$B$3 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Excel 2010

	A	B
1	Lookup	Result
2	Test_1	Result_T1.1
3	Test_2	Result_T2.1
4	Test_3	Result_T3.1
5	Test_2	Result_T2.2
6	Test_2	Result_T2.3
7	Test_2	Result_T2.4
8	Test_1	Result_T1.2

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

DATA​