Index and Match

[josieguida02]

Can someone help me with my issue?

I only want to display companies from sheet one on sheet 2 that have a match for cell A1 on sheet 2.


Current workbook formulas.

Sheet 1 last column, =IF(ISNA(MATCH(C2,{"abc","abc1","abc2","abc3"},0)),"-",MAX(I$1:I1)+1)

Date	serial number	company			Review Date		Status
5/1/2018	B12345	ABC			5/1/2018		Accepted	1
5/2/2018	B23456	ABC1			5/2/2018		Consider	2
5/3/2018	B34567	ABC2			5/3/2018		Compare Elsewhere	3
5/4/2018	B45678	ABC3			5/4/2018		Rejected	4
5/5/2018	C12345	ABC			5/4/2018		Accepted	5
5/5/2018	C23456	ABC1			5/2/2018		Consider	6
5/5/2018	D12345	ABC2			5/3/2018		Compare Elsewhere	7
5/5/2018	D24567	ABC3			5/4/2018		Rejected	8

Sheet 2

Each data point in all three columns

=IFERROR(INDEX('Sheet 1'!A:A,MATCH(ROWS($2:2),'Sheet 1'!$I:$I,0)),"")

=IFERROR(INDEX('Sheet 1'!B:B,MATCH(ROWS($2:2),'Sheet 1'!$I:$I,0)),"")

=IFERROR(INDEX('Sheet 1'!C:C,MATCH(ROWS($2:2),'Sheet 1'!$I:$I,0)),"")

ABC
date	serial number	Company
5/1/2018	B12345	ABC
5/2/2018	B23456	ABC1
5/3/2018	B34567	ABC2
5/4/2018	B45678	ABC3
5/5/2018	C12345	ABC
5/5/2018	C23456	ABC1
5/5/2018	D12345	ABC2
5/5/2018	D24567	ABC3

Thank you in advance.

Josie

 

[Eric W]

What version of Excel do you have? Please update your profile to show that. It's quite easy to do with Excel 365:

Book1
	A	B	C
1	ABC
2	Date	Serial Number	Company
3	43221	B12345	ABC
4	43225	C12345	ABC
5
Sheet9

Cell Formulas
Range	Formula
A3:C4	A3	=FILTER(Sheet8!A2:C20,Sheet8!C2:C20=A1)
Dynamic array formulas.

It's possible with older versions, but we need to know what version you have so that we know what functions are available.

 

[josieguida02]

What version of Excel do you have? Please update your profile to show that. It's quite easy to do with Excel 365:

Book1
	A	B	C
1	ABC
2	Date	Serial Number	Company
3	43221	B12345	ABC
4	43225	C12345	ABC
5
Sheet9

Cell Formulas
Range	Formula
A3:C4	A3	=FILTER(Sheet8!A2:C20,Sheet8!C2:C20=A1)
Dynamic array formulas.

It's possible with older versions, but we need to know what version you have so that we know what functions are available.

Thank you for your help.
I found this to work for me. =IFERROR(INDEX(Companies!C2,MATCH($A$1,Companies!$C2,0)),"")

 

[Eric W]

Thank you for your help.
I found this to work for me. =IFERROR(INDEX(Companies!C2,MATCH($A$1,Companies!$C2,0)),"")

Well, if you're happy, then I'm happy. But I don't think that formula will work for you. MATCH requires a range for the second parameter, and you just have a single cell.