Index Match (I think?)

[wtom0412]

Hi again,

I am really struggling with INDEX MATCH (at least that is what I think I need to use).

I have a table (R:AM) which contains 20 rows of data (I would love to attach it, but can't figure out how to, so I have abridged it below).

[TABLE="width: 590"]
<tbody>[TR]
[TD][/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]500 lookup[/TD]
[TD]500[/TD]
[TD]1000 lookup[/TD]
[TD]1000[/TD]
[TD]1500 lookup[/TD]
[TD]1500[/TD]
[TD]2000 lookup[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11500[/TD]
[TD] $       0.90[/TD]
[TD]111000[/TD]
[TD] $       0.81[/TD]
[TD]111500[/TD]
[TD] $       0.73[/TD]
[TD]112000[/TD]
[TD] $       0.66[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12500[/TD]
[TD] $       0.80[/TD]
[TD]121000[/TD]
[TD] $       0.72[/TD]
[TD]121500[/TD]
[TD] $       0.65[/TD]
[TD]122000[/TD]
[TD] $       0.58[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]13500[/TD]
[TD] $       0.75[/TD]
[TD]131000[/TD]
[TD] $       0.68[/TD]
[TD]131500[/TD]
[TD] $       0.61[/TD]
[TD]132000[/TD]
[TD] $       0.55[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]14500[/TD]
[TD] $       0.50[/TD]
[TD]141000[/TD]
[TD] $       0.45[/TD]
[TD]141500[/TD]
[TD] $       0.41[/TD]
[TD]142000[/TD]
[TD] $       0.36[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]21500[/TD]
[TD] $       1.00[/TD]
[TD]211000[/TD]
[TD] $       0.90[/TD]
[TD]211500[/TD]
[TD] $       0.81[/TD]
[TD]212000[/TD]
[TD] $       0.73[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]22500[/TD]
[TD] $       0.90[/TD]
[TD]221000[/TD]
[TD] $       0.81[/TD]
[TD]221500[/TD]
[TD] $       0.73[/TD]
[TD]222000[/TD]
[TD] $       0.66[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]31500[/TD]
[TD] $       0.80[/TD]
[TD]311000[/TD]
[TD] $       0.72[/TD]
[TD]311500[/TD]
[TD] $       0.65[/TD]
[TD]312000[/TD]
[TD] $       0.58[/TD]
[/TR]
</tbody>[/TABLE]

I want to type a value into L2 (211000 for example - this value appears at T7), and have the adjacent cell's value (U7) shown in F2 (in this instance $0.81).

Any help would be greatly appreciated.

Cheers, WT

 

[Fluff]

How about
=SUMPRODUCT(--(R2:X8=L2),(S2:Y8))

 

[tyija1995]

Another approach with INDEX MATCH and a helper value (I used cell L1 for a helper value):

=INDEX($R$1:Y8,MATCH($L$2,OFFSET($R$1,0,MATCH($L$1,$R$1:Y$1,FALSE)-1,COUNTA($R:$R)),FALSE),MATCH($L$1,$R$1:Y$1,FALSE)+1)

Where L1 contains the formula:
=IF(AND(RIGHT(L2,3)="500",LEN(L2)<=5),500,RIGHT(L2,4))&" lookup"

Note the odd use of referencing I have used; where the referencing is NOT absolute, it must be changed to suit your table needs.

E.g. $R$1:Y8 means you should change Y8 to suit your table, etc.

EDIT: I have made a few assumptions,
1) The 500 lookup contains all numbers with no more than 5 digits.
2) The # lookup does not go in to 5 digits (e.g. 10000 lookup in header column)

 

[wtom0412]

Very cool, thanks again Fluff, you have saved me three times in as many days!! I am in awe!!

If it's not too much trouble, can you explain the (--) part of the formula?

Cheers, WT

 

[wtom0412]

Thank you tyija,

It is 12:30 am here at the moment, and I fear if I try this formula at the moment, my head might explode!! I will give it a go in the morning - I think I might enjoy working this one out!!

Cheers, WT

 

[tyija1995]

Apologies, when I had copied in your data it came with dollar signs etc so I couldn't get Fluffs answer to work - hence the long winded solution I gave (Fluffs answer is a lot better though!! I just changed the values to... values and got it to work!) Ignore my answer as it makes too many assumptions and is over complicating the problem massively!

the -- Fluff used is called a unary operator and converts values in to binary (0;1) etc 0 = false, 1 = true

 

[Fluff]

You're welcome & thanks for the feedback.

The -- converts an array of TRUE, FALSE to 1,0