Need to return data from table, not sure which function to use

StuffofLegend

New Member
Joined
Oct 22, 2015
Messages
8
Ok, so I have a problem with extracting some data from an Excel table. Forgive me if I don’t explain my problem well (my Excel knowledge is limited). The layout of my spreadsheet is as follows: Individual claim items make up the rows in my main data sheet (my ‘Claim Detail’ sheet), and the specific columns that I am interested in are “Dispatch ID” (the job number, which is column A), “Item Number” (column B), “Claim Label” (which is the month of the claim, and is column F), and “External ID” (column M).

What I want to do is enter a formula on a separate sheet (‘Minor Projects’), which searches on the Claim Detail sheet for a specific type of External ID (the value must start with either an “RRT” or an “M”), and once Excel has found a cell containing either of these values, it needs to check the values of one other column within the row that the value is located: It needs to check column F and only return a value if column F is displaying the current claim month (October 2015 in this case, however I can use an absolute cell reference $A$1 to let the formula know what the value of the current claim month should be. The value in A1 will change to November 2015 next month).

Once Excel has found an External ID value starting with “RRT” or “M”, and confirms that column F within that row displays October 2015, it returns the External ID in the cell which contains the formula. A potential problem is that in any given month there will be up to 50 claim items which could have an RRT or M value, and as an example there could be 5 claim lines which all have the same M value, e.g. M1677. It is the Dispatch ID and Item Number columns which provide the extra info to show that the 5 claim lines are unique e.g. 2 of the 5 claim lines will have a Dispatch ID of 7112 and 2 will have a Dispatch ID of 7113. That still leaves two claim lines which have the same External ID, Claim Label and Dispatch ID, and it is the Item Number column which provides the unique entry.

So to summarise, once Excel has found an External ID value starting with “RRT” or “M”, and confirms that column F within that row displays October 2015, it needs to return the External ID in cell A25 of my separate sheet. Then in cell B25 of my separate sheet it needs to return the corresponding Dispatch ID and in cell C25 of my separate sheet it needs to return the corresponding Item Number. I realise I would need to copy the formulas down for approx. 50 rows because of the number of claim items each month which could have an RRT or M value. I need to ensure that none of the lines in my separate sheet are duplicates. My guess would be that I need to use a combination of the INDEX, VLOOKUP and IF functions. If anyone can help, that would be very much appreciated.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,137,113
Messages
5,679,701
Members
419,850
Latest member
Cbell07958

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