Search column A for value x, if present, return corresponding value in column B

[Iluvexcel123]

Hello,

I'm trying to asking Excel to search all values in a particular column for a specific number, and if that number is found, produce the result in the corresponding row in a separate column. I hope the formatting of the example below is clear enough to show the desired results. Thanks for your help!

Note 1: The results will display on a different worksheet.
Note 2: I don't want any blank cells before the first value found (blank cells at the end of the list are okay)

Raw Data: DESIRED RESULTS:

House Number	Characteristics		House 1	House 2	House 3	House 4
1	1 garage		1 garage	3 bedrooms	2 garages	6 windows
1	2 full bath		2 full bath	1 basement	3 full bath	4 bedrooms
1	1 half bath		1 half bath		1 half bath
2	3 bedrooms				5 bedrooms
2	1 basement
3	2 garages
3	3 full bath
3	1 half bath
3	5 bedrooms
4	6 windows
4	4 bedrooms

<tbody>
</tbody>

 

[Sinon]

Assuming that your data is in Sheet 1 range A1:B12 and your desired results in Sheet 2 with the house numbers in cells A1:D1, try this:

*	A	B	C	D
1	House 1	House 2	House 3	House 4
2	1 garage	3 bedrooms	2 garages	6 windows
3	2 full bath	1 basement	3 full bath	4 bedrooms
4	1 half bath	*	1 half bath	*
5	*	*	5 bedrooms	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A2 {=IFERROR(INDEX(Sheet1!$B$2:$B$12,SMALL(IF(Sheet1!$A$2:$A$12=(RIGHT(Sheet2!A$1,LEN(Sheet2!A$1)-SEARCH(" ",Sheet2!A$1))*1),ROW(Sheet1!$A$2:$A$12)-ROW(Sheet1!$A$2)+1),ROW(1:1))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The formula takes into account that you have text "House 1", "House 2" etc and not just 1,2 etc. I accounted for this difference in data with the following : =RIGHT(Sheet2!A$1,LEN(Sheet2!A$1)-SEARCH(" ",Sheet2!A$1))*1

Apply the formula down until it becomes blank and to the right for the 4 houses. Don't forget to adjust the range to your actual range. You can have a closer look at the formula here: Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value

 

[Iluvexcel123]

Thank you so much for your quick response! I see the instruction to do the CNTRL SHIFT ENTER. I should have mentioned that the worksheet where the results are displayed is a template created and provided by my employer. It has merged cells (which need to remain merged), and I get an error message when I try to use the CNTRL SHIFT ENTER. The error message says it can't be used with merged cells. Is there a way around this?

 

[Sinon]

Personally, I would just unmerge the cells and never again merge them (but hey I might be the only one traumatized by merged cells). An alternative would be to write the formula in a non-merged cell, let's say AA2. then in your merged cell, type in =AA2 and do the same for every merged cell you want the formula in. Alternatively, you can have a look at this article How to enter array formulas in merged cells | Get Digital Help - Microsoft Excel resource. Or even try googling some more options. I avoid merged cells at all costs so I am not very familiar with the issue.

 

[Iluvexcel123]

Hello Sinon,

Your solution worked!! This will make processing tasks soooo much easier. Thank you so much!