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

donghan82

New Member
Joined
Dec 19, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:

1608426804412.png




Snapshot 2:
1608427032745.png







Any advice would help.

Thank you.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,391
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLM
1ComponentFieldBase ProjectGeneratorMax
2MCC:ACC-4MBDBus180.422AF1AfF2VoltageAF3AF4
3AF120.523MCC:ACC-4MBD23456
4AfF230.624MCC:ACC-1MDBA89111214
5Voltage40.725
6AF350.826
7AF460.927
8MCC:ACC-1MDBABus70.628
9AF180.729
10AfF290.830
11Voltage110.1131
12AF3120.2232
13AF4140.3333
Sheet1
Cell Formulas
RangeFormula
I3:M4I3=OFFSET($A$1,MATCH($H3,$A$2:$A$9999,0)+COLUMNS($I$2:I2),2)
 

donghan82

New Member
Joined
Dec 19, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 19, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank you for your help. You can disregard my follow up question as the cell formula you provided worked.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,349
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top