Match, Index, VLookup, Hlookup

[lricher]

Hi all,

I previously requested help on a different forum but but am still no wiser.

I am trying to identify the value of label at the top column for a value selected within the core of a table.

Using Match(1300,B5:I5,-1) returns value 7, which is the value I am interested in. Note that Column 1 is 'B'.

Having found 7, i.e. Column number 7 starting from ‘B’, I need to know the value at the top of Column 7. And this is where I am stuck.

	A	B	C	D	E	F	G	H	I
1		Type 1	Type 2	Type 3	Type 4	Type 5	Type 6	Type 7	Type 8
2	Level 1	UL	2,232	1,488	2,232	1,488	1,907	1,116	651
3	Level 2	UL	UL	UL	UL	UL	UL	UL	UL
4	Level 3	4,464	2,418	1,628	2,418	1,628	2,372	1,302	837
5	Level 4	7,347	3,627	2,418	3,627	2,418	3,581	1,953	1,256

Anyone has any idea? i think i basically need a function that allows me to return the value "Type 7" after using the afforested MATCH function.

Many thanks.

 

[NoS]

If your formula was MATCH(3500,B5:I5,-1) what column would you expect your answer to be from ?

I don't think that formula is going to work for you as it would require the search range to be sorted in descending order, which it isn't.
see this.

But none the less, Match will return the column number within the specified range, so the formula you want could be like this
=INDEX(B1:I1,1,MATCH(1300,B5:I5,-1))

 

[lricher]

Thanks i will try this.

In answer to your question, the answer to the formula MATCH(3500,B5:I5,-1) is "Type 4", as i would need the closest match from the right to the left (if this makes sense)

Lou

 

[NoS]

No, it doesn't make sense because the 3,581 under Type 6 is the smallest value larger than 3500.

When you actually try the formula on your posted data it returns 2.
This is why the search data being sorted in descending value is a requirement of using the Match function.

I don't think Match is the function you're in need of, but must confess don't know what function is.