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:

[TABLE="width: 621"]
<tbody>[TR]
[TD]House Number[/TD]
[TD]Characteristics[/TD]
[TD][/TD]
[TD]House 1[/TD]
[TD]House 2[/TD]
[TD]House 3[/TD]
[TD]House 4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1 garage[/TD]
[TD][/TD]
[TD]1 garage[/TD]
[TD]3 bedrooms[/TD]
[TD]2 garages[/TD]
[TD]6 windows[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2 full bath[/TD]
[TD][/TD]
[TD]2 full bath[/TD]
[TD]1 basement[/TD]
[TD]3 full bath[/TD]
[TD]4 bedrooms[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1 half bath[/TD]
[TD][/TD]
[TD]1 half bath[/TD]
[TD][/TD]
[TD]1 half bath[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3 bedrooms[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5 bedrooms[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1 basement[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2 garages[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3 full bath[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1 half bath[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5 bedrooms[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6 windows[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4 bedrooms[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
House 1	House 2	House 3	House 4
1 garage	3 bedrooms	2 garages	6 windows
2 full bath	1 basement	3 full bath	4 bedrooms
1 half bath	*	1 half bath	*
*	*	5 bedrooms	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</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!