PRICE SEARCH using KEY words and EXACT

[makinmomb]

I have following data on SHEET1

TEXT CODE	DESCRIPTION	CODE	PRICE
431 STAR	SHOWER SET	101.000.23	9,480
431 CARINA	SHOWER SET	101.000.24	9,605
461 STAR	SHOWER SET	101.000.21	18,125
461 CARINA	SHOWER SET	101.000.22	18,325

<colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"><colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"><colgroup><col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"><colgroup><col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"><tbody>
</tbody>

I have following on SHEET2 as my results sheet

ENTER KEY WORD
TEXT CODE	DESCRIPTION	CODE	PRICE

<colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"><colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"><colgroup><col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"><colgroup><col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"><tbody>
</tbody>

What is the formula on cell A4 to display results depending on input on cell C2

Notes : If I type 431 should dispay as follows , can the formula also handle exact match if type not 431 instead 431 STAR

TEXT CODE	DESCRIPTION	CODE	PRICE
431 STAR	SHOWER SET	101.000.23	9,480
431 CARINA	SHOWER SET	101.000.24	9,605

<colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"> <tbody> </tbody>

 

[Aladin Akyurek]

SHEET2 (What is wrong with "Sheet2"?)

F4, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(SHEET1!$A$2:$A$5=$C$2,
    ROW(SHEET1!$A$2:$A$5)-ROW(SHEET1!$A$2)+1),ROWS($F$4:F4)),"")

A4, just enter, copy across to D4, and down:

=IF($F4="","",INDEX(SHEET1!A$2:A$5,$F4))

 

[makinmomb]

SHEET2 (What is wrong with "Sheet2"?)

F4, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(SHEET1!$A$2:$A$5=$C$2,
    ROW(SHEET1!$A$2:$A$5)-ROW(SHEET1!$A$2)+1),ROWS($F$4:F4)),"")

A4, just enter, copy across to D4, and down:

=IF($F4="","",INDEX(SHEET1!A$2:A$5,$F4))

XXX

Ouch , almost there , did you mean I write two formulas , one on cell f4 and one on cell A4, slightly not getting , the input is on cell C1 and C2

Please re assist , I am already enjoying the answer of tile program with answer, MR EXCEL sight has definitely changed my year 2014, I wish I knew of your site earlier, WOW team

=IFERROR(INDEX(data,SMALL(IF(INDEX(data,,MATCH($B$1,Sheet1!$A$1:$L$1,0))=$A$1,IF(INDEX(data,,MATCH($B$2,Sheet1!$A$1:$L$1,0))=$A$2,
ROW(data)-ROW(INDEX(data,1,1))+1)),ROWS($A$5:$A5)),COLUMNS($A$5:A$5)),"")

 

[makinmomb]

Input at cell C1 and NOT C2 though I edited the formula,
not working , I am so curious to see the working result, not sure where I am wrong

 

[Aladin Akyurek]

ADVERTISEMENT

Input at cell C1 and NOT C2 though I edited the formula,
not working , I am so curious to see the working result, not sure where I am wrong

Why not follow the instructions as given?

 

[makinmomb]

Why not follow the instructions as given?

I am trying to follow your exact instructions

it gives me a wrong result

ENTER KEY WORD	431
TEXT CODE	DESCRIPTION	CODE	PRICE
461 STAR	SHOWER SET	101.000.21	18125		0

<colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"><colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"><colgroup><col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"><colgroup><col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"><colgroup><col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"><colgroup><col width="14" style="width: 11pt; mso-width-source: userset; mso-width-alt: 512;"><tbody>
</tbody>

the formula on cell a4 is =IF($F4="","",INDEX(Sheet1!A$2:A$5,$F4))
the formula on cell a4 is =IFERROR(SMALL(IF(Sheet1!$A$2:$A$5=$C$1,ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS($F$4:F4)),"")

 

[makinmomb]

ADVERTISEMENT

the formula on cell a4 is =IF($F4="","",INDEX(Sheet1!A$2:A$5,$F4))
the formula on cell F4 is =IFERROR(SMALL(IF(Sheet1!$A$2:$A$5=$C$1,ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS($F$4:F4)),"")

 

[Aladin Akyurek]

the formula on cell a4 is =IF($F4="","",INDEX(Sheet1!A$2:A$5,$F4))
the formula on cell F4 is =IFERROR(SMALL(IF(Sheet1!$A$2:$A$5=$C$1,ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS($F$4:F4)),"")

The one in F4 needs to be confirmed with control+shift+enter and copied down.

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key.

 

[makinmomb]

The one in F4 needs to be confirmed with control+shift+enter and copied down.

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key.

Is it correct that when I press CTRL SHIFT ENTER the formula than closes with signs { } , almost there , oops

 

[makinmomb]

Is it correct that when I press CTRL SHIFT ENTER the formula than closes with signs { } , almost there , oops

the formula thus now reads as follows with the CTRL SHIFT ENTER used

a4 = =IF($F4="","",INDEX(Sheet1!A$2:A$5,$F4))
f4 = {=IFERROR(SMALL(IF(Sheet1!$A$2:$A$5=$C$1,ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS($F$4:F4)),"")}

The result being blank as

ENTER KEY WORD	461
TEXT CODE	DESCRIPTION	CODE	PRICE

<colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="14" style="width: 11pt; mso-width-source: userset; mso-width-alt: 512;"> <tbody> </tbody>