Data Match and index across 3 colums

darrensconfused

New Member
Joined
Apr 21, 2019
Messages
10
Hello Everybody

I have a workbook i am trying to match the data from one table (sheet 1 Table) to another (sheet 2 Table) and produce the cost to the matching data. The table on sheet one has drop down cells with all the various sizes so i am hoping that once a combination that matches the table on sheet 2 it will display a cost from the match on sheet 2.

Sheet 1 Table


I have used the following formula successfully to match the data from one colum but cant get it to work across all three columns.

=INDEX(Sheet2!G42:J66,1,4,MATCH(E26,Sheet2!G42:G66,0))

you can download the workbook at this link if required

https://drive.google.com/open?id=15DLYUjbdXd1Naqt63YVGlwS3DR5MdtZh

Thanks very much in advance and i hope i have provided enough information
 
Last edited by a moderator:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
You data in Sheet2:

Book1
ABCDEF
2CodeDescriptionSize 1Size 2Size 3Cost
33200051-1MULTISNAP Y F/INSUL20015015010
43200053-1MULTISNAP Y F/INSUL25015015020
53200054-1MULTISNAP Y F/INSUL25020015030
63200055-1MULTISNAP Y F/INSUL25020020040
73200058-1MULTISNAP Y F/INSUL30020020015
83200059-1MULTISNAP Y F/INSUL30025020025
93200060-1MULTISNAP Y F/INSUL30025025035
103200062-1MULTISNAP Y F/INSUL35025025020
113200063-1MULTISNAP Y F/INSUL35030020030
123200064-1MULTISNAP Y F/INSUL35030025040
133200065-1MULTISNAP Y F/INSUL35030030050
143200067-1MULTISNAP Y F/INSUL40030030025
153200068-1MULTISNAP Y F/INSUL40035025035
163200069-1MULTISNAP Y F/INSUL40035030045
173200070-1MULTISNAP Y F/INSUL40035035055
183200072-1MULTISNAP Y F/INSUL45035030030
193200073-1MULTISNAP Y F/INSUL45035035040
203200074-1MULTISNAP Y F/INSUL45040030050
213200075-1MULTISNAP Y F/INSUL45040035060
223202044-1MULTISNAP Y F/INSUL45040040070
233202045-1MULTISNAP Y F/INSUL50040035035
243202046-1MULTISNAP Y F/INSUL50040040045
253202047-1MULTISNAP Y F/INSUL50045035055
263202048-1MULTISNAP Y F/INSUL50045040065
273202049-1MULTISNAP Y F/INSUL50045045075
Sheet2


Your answer in sheet1:


Book1
ABCDEFGH
1Multi Snap Insulated Y Pieces
2QnttyCodeDescriptionSize 1Size 2Size 3Our CostTotal
33200051-1MULTISNAP Y F/INSUL200200200#N/A#N/A
43200053-1MULTISNAP Y F/INSUL250150150200
53200054-1MULTISNAP Y F/INSUL200150150100
6MULTISNAP Y F/INSUL200150150100
7MULTISNAP Y F/INSUL200150150100
8MULTISNAP Y F/INSUL200150150100
9MULTISNAP Y F/INSUL200150150100
10MULTISNAP Y F/INSUL200150150100
11MULTISNAP Y F/INSUL200150150100
123200055-1MULTISNAP Y F/INSUL200150150100
13Y piece Totals#N/A
Sheet1
Cell Formulas
RangeFormula
G3{=INDEX(Sheet2!$F$3:$F$27,MATCH(D3&E3&F3,Sheet2!$C$3:$C$27&Sheet2!$D$3:$D$27&Sheet2!$E$3:$E$27,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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