Using INDEX and MATCH

[estein]

Hello everyone, I have two active worksheets. The first one is called 'Chart' and the second is 'Values'.

I have three columns of data. I would like to look in the first two, and return a value in the third.


=INDEX(Values!$C$1:$C$299,MATCH(Values!$D$1:$D$299,LEFT(Chart!A1:A16091,6)&" "&LEFT(Chart!C1:C16091,2)),0)

Values!$C$1:$C$299 => Result Matrix

Values!$D$1:$D$299 => Result of the concatenation of Column 1 with Column 2. So the result is like Column1_Space_Column2

LEFT(Chart!A1:A16091,6) => Cloumn1

LEFT(Chart!C1:C16091,2) => Cloumn2

I suspect that I am doing something wrong in the highlighted area. Any help is appreciated.

[Richard Buttrey]

Hi,

It would be easier to comment if we had an example, but if I've understood you correctly, I think you need a helper column which concatenates the values in the first two columns. You can then use a standard VLOOKUP, to search this helper column and return the value from the third column.

HTH

[estein]

For E.g. in Match formula, MATCH(lookup_value,lookup_array,match_type)

Can I use a range of arrays ($D$1:$D$299) for the lookup_value ? If there is a way around, then my problem is solved.

-------------------------------------------------------------------------------------------------------------------------

As you have mentioned, I tried using VLOOKUP

=VLOOKUP(LEFT(Chart!A1:A16091,6)&" "&LEFT(Chart!C1:C16091,2),Values!C:D,2,FALSE)

I got it!
Thank you for your help.