# VLookup, Index, match or something else needed please.

#### GAJITCS

##### New Member
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>

#### Jonmo1

##### MrExcel MVP
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

##### New Member
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.

You're welcome.

1,082,143
Messages
5,363,378
Members
400,732
Latest member
robcooper2001

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...