Need help on creating an excel formula to search a particular item and extracting the required information

[donghan82]

Good evening,

I need help creating an excel formula that searches for the "component" name and extracts each value for the following: "AF Worst Case Working", "AF Worst Case Incident", Voltage (V), "AF Worst Case Boundary", "AF_LimitedApproach", through "AF_BoltedFault (kA)". Refer to snapshot 1. The extracted information would then go into a separate excel file. Refer to Snapshot 2.

I tried using index(match()), hlookup(), and vlookup(), but I haven't been successful with these commands to give me what I needed to see.


Snapshot 1:

Snapshot 2:

Any advice would help.

Thank you.

 

[Toadstool]

Hi Donghan,

Do all the rows for a component always exist and in the same order? If so you can use OFFSET and MATCH.

DongHan82.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Component	Field	Base Project	Generator	Max
2	MCC:ACC-4MBD	Bus	18	0.4	22				AF1	AfF2	Voltage	AF3	AF4
3		AF1	2	0.5	23			MCC:ACC-4MBD	2	3	4	5	6
4		AfF2	3	0.6	24			MCC:ACC-1MDBA	8	9	11	12	14
5		Voltage	4	0.7	25
6		AF3	5	0.8	26
7		AF4	6	0.9	27
8	MCC:ACC-1MDBA	Bus	7	0.6	28
9		AF1	8	0.7	29
10		AfF2	9	0.8	30
11		Voltage	11	0.11	31
12		AF3	12	0.22	32
13		AF4	14	0.33	33
Sheet1

Cell Formulas
Range	Formula
I3:M4	I3	=OFFSET($A$1,MATCH($H3,$A$2:$A$9999,0)+COLUMNS($I$2:I2),2)

 

[donghan82]

Yes, information shown in snapshot 1 will always remain in that format. Please note, Snapshot 1 is one excel file and Snapshot 2 is a separate excel file.

Would I be able to add the "index()" command with the cell formula you provided?
- The content under the "Bus Name" column from Snapshot 2 will be manually entered and will need to search and match with information shown under the "component" column from Snapshot 1.

 

[donghan82]

Thank you for your help. You can disregard my follow up question as the cell formula you provided worked.