Index and Matching 2 different tables or multiple tables

[Buddhaboy32]

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.

 

[OceanStateSixSpeed]

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]

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]

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