Lookup problem

[Zaigham]

Hi
With reference to my previous thread “FIND COLUMN NUMBER” at http://www.mrexcel.com/forum/showthread.php?t=561419&goto=newpost mainly with INDEX() I have achieved my goal to find a certain amount on point to point basis in a different row and column.
Unfortunately at other step I am again in dark. My second goal was to find a certain value in a higher stage. I was thinking that it would be done easily with the help of =VLOOKUP() or =HLOOKUP() but I am unable to find my way. I have uploaded my file at http://www.mediafire.com/?wf9d22fycm2j400 .
I need more help.

 

[jasonb75]

Not sure exactly what you're asking for. Is this anywhere close?

Excel Workbook
	B	C	D	E	F	G	H
2	BPS	0	1	2	3	4	5
3	1E	2,970	3,060	3,150	3,240	3,330	3,420
4	1P	4,345	4,490	4,635	4,780	4,925	5,070
5	2E	3,035	3,135	3,235	3,335	3,435	3,535
6	2P	4,855	5,015	5,175	5,335	5,495	5,655
7	3E	3,140	3,260	3,380	3,500	3,620	3,740
8	3P	5,020	5,210	5,400	5,590	5,780	5,970
9	4E	3,240	3,380	3,520	3,660	3,800	3,940
10	4P	5,180	5,400	5,620	5,840	6,060	6,280
11	5E	3,340	3,500	3,660	3,820	3,980	4,140
12	5P	5,340	5,595	5,850	6,105	6,360	6,615
13
14
15	3E	3500	3520
16	2E	3535	3620
Sheet1

 

[taurean]

Same here but if you want your value to reflect closest match then try following formula for your request in PayScale2011 file:
=LOOKUP(H39,F37:Z37)

 

[Zaigham]

"Thanks both guys, first I will ask "taurean" my lookup value is in cell "O37", so =LOOKUP(O37,F39:Z39) points to cell "G39" i.e., one step before which I want to extract. I need to point the value in cell "H39" in stead of "G39".

Secondly "jasonb75" in your solution is also close but I want if I input 3620 the in any row beneath the row 7 must be closed (greater than or equal to) 3620 say cell F6 = 3660 or cell C10=5180 or cell cell E11=3660. Please note that user input value are 3660 and a row heading i.e., 3E, 4E etc.

 

[taurean]

This kept me awake for 30 minutes more than my usual bed-time so I'd be really cross if this isn't it:
=LARGE($F39:$Z39,SUMPRODUCT(--($F39:$Z39>O$37)*1))

 

[Zaigham]

This kept me awake for 30 minutes more than my usual bed-time so I'd be really cross if this isn't it:
=LARGE($F39:$Z39,SUMPRODUCT(--($F39:$Z39>O$37)*1))

Hi taurean,

It works. Even though I have found solution on your previous simple advise. Because I am not well conversant with Excel or any other spreadsheet so my formula was too long =IF(ISNA(LOOKUP(N49,INDIRECT("F"&E55&":AJ"&E55))),INDIRECT("F"&E55),IF(LOOKUP(N49,INDIRECT("F"&E55&":AJ"&E55))>=N49,LOOKUP(N49,INDIRECT("F"&E55&":AJ"&E55)),LOOKUP(N49,INDIRECT("F"&E55&":AJ"&E55))+INDIRECT("C"&E55)))
You have again advised a simple formula. I really appreciate your sacrifice of your bedtime. Just thanks is not enough but what else we can do so Thank you very very much. God bless you.

Zaigham

 

[taurean]

Glad that it helps. And it makes burning the night's oil worth.

The problem with LOOKUP is that it gives value which is lower than the VALUE being looked up. Had it been otherwise we would have a simple formula on our hands.

Just to explain what it does:
a. The function LARGE gives nth largest number in the specified array.
b. SUMPRODUCT gives COUNT of values larger than LOOKUP VALUE. So that is immediate larger value than LOOKUP VALUE.

 

[jasonb75]

Isn't that the same as

{=MIN(IF($F39:$Z39>O$37,$F39:$Z39))}

 

[Zaigham]

Yes both the commands gives the same result. However, by chance I have reached to a condition where lookup array has a value equal to lookup value. In this condition the answer is obviously greater than lookup value whereas I need the figure equal to or greater than lookup value.
Any ways thanks for your contribution.

 

[jasonb75]

One possible alternative

{=small(if($F39:$Z39>=O$37,$F39:$Z39),2)}