Help in LOOKUP Formula.

srizki

Well-known Member
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 B C D JN 3 PV 3 JN 3 7039300 JN 3 9315547 JN 3 10563581 JN 3 10897404 JN 3 10920743 JN 3 10920755 JN 3 10920762 II 7039300 10920763 OV 10563581 10933408

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

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

Thanks

Sohail

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try

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

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

Last edited:
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

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

Thanks Martin.

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.

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

Thank you very much Jonmo1

Really appreciated.

You must be an Aggie.

Regards

Sohail Rizki

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.

Sorry,
Sohail

Replies
7
Views
258
Replies
0
Views
791
Replies
4
Views
141
Replies
6
Views
232
Replies
8
Views
229

1,203,101
Messages
6,053,532
Members
444,670
Latest member
laurenmjones1111

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back