Table searching

ExcelEddie

New Member
Joined
Feb 18, 2013
Messages
22
Hi Team,

Sample Table:

A B C D
1 Ref. No. Name Area Comment
2 123456 John Area 1 Comment 1
3 123456 John Area 2 Comment 2
4 123456 John Area 1 Comment 3
5 123564 Mary Area 2 Comment 3

Outside of the above table I have data showing that of columns A, B and C in drop down boxes if that helps.

Can I pull Comment 2 cell ref (D3) from the above example table using a mix of Index and Match (Office 365) or is there another way?

Thank you in advance people.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Can you be more explicit with what your criteria is for pulling data?
Kind of looks like you may a Reference Number, Name and Area in another table and want to perform a lookup based on those three fields matching with your array above, then pull from column D. Is that correct?
 
Upvote 0
13261Fred BloggsOffshoreProblem ManagementMar-23Case Management (Applied to all areas)D-02 Converted to a Known ErrorSwap space exhaust
13261Fred BloggsOffshoreProblem ManagementMar-23Problem DetailsA-10 Root CauseThe Swap space was exhausted, no mention of proper details

Above are two rows from the actual table.

I need to have the contents (The Swap space was exhausted...) on another cell on a report.
I have the 13261, Fred Bloggs and 'A-10 Root Cause' available to me on that report so I reckon that these can be used in a formula of some sort in order to pull the comment (The Swap space was exhausted...)


The report looks like this:

1678458225094.png


I need the above comment to appear in the box titled Observer Comment. I've tried Match and Index and yes now I have a headache ;)
 
Upvote 0
I got it:

{=INDEX(L4:L203,MATCH(1,(B4:B203=D7)*(H4:H203=H29),0))}

L4:L203 is the column that contains the result
B4-B203 is the column that contains criteria 1 (cell D7)
H4:H203 is the column that contains criteria 2 (cell H29)

Cells D7 and H29 are drop down list boxes, in my case one holds a reference number and the other a piece of data relating to that reference number.
The reference number can appear multiple times in the table but will have different relational data each time
The result is a comment relating to that data only.

Hope this helps someone.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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
Back
Top