Index and Matching 2 different tables or multiple tables

Buddhaboy32

New Member
Joined
Apr 15, 2020
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Good day

I need to index / match multiple tables to get a result in IMAGE 1.

EG: If Item FCT_8004_CER is selected where the pressure = 7.6 BAR then L/MIN = 2.82

SEE IMAGE 2 for the 1st table

I got it working with a single table.

My problem comes in with the 2nd table. See IMAGE 3

The values for the BARS and L/Min differ in table 1 (Image 2) and table 2 (Image 3) as does the type of nozzle.

So, what i am trying to achieve in IMAGE 1 is that if I select the following:

Category = FULL CONE Then CODE = FCT_80_04_CER then Part Number = 8259381
TYPE = MAG-CH-04 and Colour = Red with Image
BAR (Drop List) = 3.4 (Values in Table 2) (I304:N304) and L/Min = Corresponding Value (eg. 1.94) (value shows automatically)

See image 4.

I need to do the same for image 3.
 

Attachments

  • IMAGE 2.jpg
    IMAGE 2.jpg
    165.8 KB · Views: 5
  • image 3.jpg
    image 3.jpg
    147.1 KB · Views: 5
  • image 1.jpg
    image 1.jpg
    90.5 KB · Views: 3
  • Image 4.jpg
    Image 4.jpg
    89.7 KB · Views: 3

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

OceanStateSixSpeed

New Member
Joined
Jul 24, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
It would be helpful to see the formulas you're using.

So IMAGE2 abd 3 are in two different input tables, right? You would probably have to build the name of the tab into your formulas, e.g.
=IF(A1="Hollow Cone"; HLOOKUP( ... lookup reference ...); IF(A1 = "Full Cone"; HLOOKUP( ... lookup reference ...)))

You could also try to find some clever way to rename you tabs "Hollow Cone" and "Full Cone" and do something like:
=HLOOKUP(B1;INDIRECT("'"&A1&"'!I1:N300");MATCH(C1;INDIRECT(("'"&A1&"'!F1:F300")))

if A1 is cone type, B1 is your BAR value, C1 is the part number
 

Buddhaboy32

New Member
Joined
Apr 15, 2020
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Hi OceanStateSixSpeed

Thank you for your answer and reply.

In image 1 and 4 regarding the Bar and L/Min I currently don't have any formulae.
I only have a drop list for the Bar value.
All the other stuff is working very well.

I will try it out and let you know if it works.
 

Buddhaboy32

New Member
Joined
Apr 15, 2020
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Hi OceanStateSixSpeed

This is the formula I used to get it to work from 1 table.

I created a drop list for the Bar Value and inserted the below formula in the L/Min Value Cell. So that when I selected a Bar pressure for a particular size nozzle it automatically showed me the L/Min corresponding to that pressure and nozzle.

=INDEX(Sheet2!$I304:$N$313,MATCH($B$5,Sheet2!$F$304:$F$313,0),MATCH($B$9,Sheet2!$I$304:LB304,0))

Hope this helps
 

Watch MrExcel Video

Forum statistics

Threads
1,123,333
Messages
5,601,008
Members
414,421
Latest member
tonybear1994

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