Help in LOOKUP Formula.

[srizki]

I am trying to get a lookup valuse but my LOOKUP formula is not working correctly. What I am doing wrong?
My formula

=LOOKUP(9.99999999999999E+307,SEARCH(D2,B2:B4063),A2:A4063)

<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

Resides in column C to see lookup value in column D and match that value in column B and get the corresponding value from column A.

A</SPAN>	B</SPAN>	C </SPAN>	D</SPAN>
JN</SPAN>	3</SPAN>	PV</SPAN>	3</SPAN>
JN</SPAN>	3</SPAN>		7039300</SPAN>
JN</SPAN>	3</SPAN>		9315547</SPAN>
JN</SPAN>	3</SPAN>		10563581</SPAN>
JN</SPAN>	3</SPAN>		10897404</SPAN>
JN</SPAN>	3</SPAN>		10920743</SPAN>
JN</SPAN>	3</SPAN>		10920755</SPAN>
JN</SPAN>	3</SPAN>		10920762</SPAN>
II</SPAN>	7039300</SPAN>		10920763</SPAN>
OV</SPAN>	10563581</SPAN>		10933408</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=4></COLGROUP>

As it can bee seen from the table above that tge frmula should bring JN.

Thanks

Sohail

 

[Jonmo1]

Try

=INDEX($A$2:$A$4063,MATCH(D5,$B$2:$B$4063,0))

 

[srizki]

Thanks Jonmo1
So, INDEX should only use one column?, because I was using
=INDEX(A1:B4063,MATCH(D2,B2:B4063,0))
and that did not work, because I added column B in it.

Thanks

 

[martindwilson]

why not use index/match? index($A$2:$A$4063,match(d2,$B$2:$B$4063,0))
if you want the last value tho
=LOOKUP(2,1/($B$2:$B$4063=D2),$A$2:$A$4063)

=INDEX(A2:B4063,MATCH(D2,B2:B4063,0),1) would have worked though

 

[srizki]

Oh ok, so lookup 2,1 is same as 9.99E+307,

Thanks Martin.

 

[Jonmo1]

Thanks Jonmo1
So, INDEX should only use one column?, because I was using
=INDEX(A1:B4063,MATCH(D2,B2:B4063,0))
and that did not work, because I added column B in it.

Thanks

That didn't work because you were indexing a 2D array A1:B4063 (more than 1 column AND more than 1 row), but you only specified a Row with the match.
You have to also specify the column on a 2D array
=INDEX(A1:B4063,MATCH(D2,B2:B4063,0),1)
The 1 specifies the column, the Match specifies the row.

If you only index a 1D array A1:A4063 (only 1 column, and multiple rows), then you don't need to specify both Row and Column.
If the array is a single column reference (A1:A4063), then your Match is assumed to be a row reference because there is only 1 column
If the array is a single row reference (A1:Z1), then your match is assumed to be a column reference because there is only 1 row.


Hope that clears it up.

 

[martindwilson]

Oh ok, so lookup 2,1 is same as 9.99E+307
no it's not look at the formula carefully
its lookup 2 in the array 1/($B$2:$B$4063=D2) which will give an array of 1's and #DIV/0!
eg
{1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!} each 1 is where there is a match
so lookup 2 will find the last 1 in the array in that example position 5 (lookup ignores error value like #DIV/0!)
its just that 2 is bigger than 1

 

[srizki]

Thank you very much Jonmo1

Really appreciated.

You must be an Aggie.

Regards

Sohail Rizki

 

[Jonmo1]

Glad to help, thanks for the feedback.

Thank you very much Jonmo1

Really appreciated.

You must be an Aggie.

Regards

Sohail Rizki

Definately NOT a Horn.

 

[srizki]

Thank you Martin, your reply was not there when I thanked Jonmo1, I was going through this thread and found that your reply went without thank you.

Sorry,
Sohail