VLookup, Index, match or something else needed please.

[GAJITCS]

I have the following table to search: A1:D10

ISO	Start	End	CHG
DE	1	1	1
DE	10	20	2
DE	21	30	4
DE	40	55	5
DK	10	20	8
DK	21	30	9
EE	21	30	3
EE	40	55	6
EE	56	56	7

<tbody>
</tbody>

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.

ISO	PC	CHG
DE	1
DE	10
DE	44
DE	20
DE	31
DK	3
DK	10
DK	15
DK	30
DK	31
EE	1
EE	21
EE	55
EE	56
EE	57

<tbody>
</tbody>

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.