VLookup, Index, match or something else needed please.

GAJITCS

New Member
Joined
Apr 21, 2015
Messages
42
I have the following table to search: A1:D10

ISOStartEndCHG
DE111
DE10202
DE21304
DE40555
DK10208
DK21309
EE21303
EE40556
EE56567

<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.

ISOPCCHG
DE1
DE10
DE44
DE20
DE31
DK3
DK10
DK15
DK30
DK31
EE1
EE21
EE55
EE56
EE57

<tbody>
</tbody>


I am looking for Formula based answers is possible please.


Many thanks for your time.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Apr 21, 2015
Messages
42
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top