VLookup, Index, match or something else needed please.

[GAJITCS]

I have the following table to search: A1:D10

[TABLE="width: 500"]
<tbody>[TR]
[TD]ISO[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]CHG[/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]21[/TD]
[TD]30[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]40[/TD]
[TD]55[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]21[/TD]
[TD]30[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]21[/TD]
[TD]30[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]40[/TD]
[TD]55[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]56[/TD]
[TD]56[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

For each of these records, I need to return the CHG value from column D where from the table below, the ISO field is a match and the PC field is between the Start and End values inclusive.

That is to say:

ISO = DE and PC = 1 in the table below should bring back the value of 1 from D2 above.
ISO = DE and PC = 44 in the table below should bring back the value of 5 from D5 above.
ISO = DK and PC = 3 in the table below should not return a value from the table above.
ISO = EE and PC = 55 in the table below should bring back the value of 6 from D9 above.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ISO[/TD]
[TD]PC[/TD]
[TD]CHG[/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]56[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]57[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am looking for Formula based answers is possible please.


Many thanks for your time.

 

[Jonmo1]

The 1st table you said is in A1:D10

The 2nd table, I put in H1:J16

Formula in J2 is
=IFERROR(INDEX($D$2:$D$10,MATCH(1,INDEX(($A$2:$A$10=H2)*(I2>=$B$2:$B$10)*(I2<=$C$2:$C$10),0),0)),"")

 

[GAJITCS]

The 1st table you said is in A1:D10

The 2nd table, I put in H1:J16

Formula in J2 is
=IFERROR(INDEX($D$2:$D$10,MATCH(1,INDEX(($A$2:$A$10=H2)*(I2>=$B$2:$B$10)*(I2<=$C$2:$C$10),0),0)),"")

Many thanks. That does exactly what I need of it.

The more I do, the more I realise two things. 1) How capable Excel is. 2) How I've only scratched the surface.

My next task is to understand why this works.

 

[Jonmo1]

You're welcome.