Lookup returns blank even though lookup_value exists in lookup_vector

[RikkiS]

Hi !

I have a worksheet which needs to lookup certain value from another sheet on the same workbook. However, it is returning blank. The original sheet does have blank cells in the lookup_vector - so I tried filling up the list but still Im getting a blank result.

Where the lookup formula is:
(Note : the forumula was originally (=lookup(D9,Total SO DCL!$D$9:$D$10000,Total SO DCL!$A$9:$A$10000). But since that was also giving me blank results I tried the other way).

Excel Query March 25, 2019 Page 1.JPG

The lookup_vector it is referencing and the result_vector.Excel Query March 25, 2019 Page 2.JPG

Ive used lookup many times. In fact, this workbook is full of lookup and VLookup formulae. But I cant for the life of me figure out why in this particular instance there are blank results.

Help would be appreciated.

Thanks a million.

RikkiS

[TMS]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[dosydos]

why not use just a vlookup formula or an Index Match formula instead?

=vlookup(D9,'Total SO DCL'!$D$9:$M$10000,10,0)

=index('Total SO DCL'!$M$9:$M$10000,match(D9,'Total SO DCL'!$D$9:$D$10000,0))

[Special-K]

If you use LOOKUP (sometimes) the data has to be in order.
You're better off changing them to VLOOKUP.

You're even better off posting the file rather than images as we can't visually check that the data is what it looks like in an image.

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[RikkiS]

Hi !

Thanks for guiding me.

Attached herewith the two sheets (one one workbook) -

a. BDL sheet is where the formula is and I want the result.
b. Total SO DCL is where the lookup_vector and result_vector is.
c. I have filled yellow on the number '01674' which is the lookup_value and is there in the lookup_vector. I have marked 3/17/2019 in Blue which is the result I was expecting.

Thanks

[Special-K]

Wrong formula, As the column you want to retrieve is on the left of the lookup value use this instead

=INDEX('Total SO DCL'!A$10:A$100000,MATCH(D9,'Total SO DCL'!$D$10:$D$10000,0),1)

It should be VLOOKUP not LOOKUP and you can only retrieve values that are on the right of the first VLOOKUP column, so column D onwards.
You want column A so you cant use VLOOKUP.

[RikkiS]

Are you saying that Lookup also has the same limitation as VLookup i.e. it cannot return values to the left of the lookup_vector?

[Special-K]

I dont usually use LOOKUP so am not that familiar without, but I did a test and see what you mean and it does seem to work - but only if the data is sorted.

According to this page

the range has to be in order. It's not, there are spaces between each code in column D.

[RikkiS]

Thanks a million. Your suggested correction worked.

Although......now, whenever there is no match, it returns #N/A (which is default in Excel - I know) and I wish it to remain blank if that is the case.

In the past, I would use =if(A1<>"",A1,"") i.e. if A1 has a value, then A1, if it doesnt leave the cell blank. Even if I used formulae, if there was no result, it would leave the cell blank. not in this case. In fact, I tried using a second if function to see if the match wasnt there in the first array, was it there in the second? And it would still return N/A since it wasnt there in the first array(even though it was there in the second).

I could use the sheet with the NA displaying - and work around that for the data that I actually need. But its just not very neat.

[Special-K]

Use

IFERROR(formula, "")

[RikkiS]

Thanks.

I was able to find the ISNumber function which worked for me. But I think Iserror would have worked better. For next time.

Thanks a million for your help.

[RikkiS]

To dosydos:

Thanks. But the Vlookup wasnt working either. I ended up using the Index, Match function.

Thanks.

[RikkiS]

why not use just a vlookup formula or an Index Match formula instead?

=vlookup(D9,'Total SO DCL'!$D$9:$M$10000,10,0)

=index('Total SO DCL'!$M$9:$M$10000,match(D9,'Total SO DCL'!$D$9:$D$10000,0))

Thanks. But the Vlookup wasnt working either.

I ended up using the Index, Match function.

Thanks for your help.

[Bo_Ry]

Please try
=IF(D9<>"",LOOKUP(2,1/('Total SO DCL'!$D$9:$D$10000=D9),'Total SO DCL'!$A$9:$A$10000),"")