VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

I'm using VLOOKUP function to match tasks (one worksheet) to assets (another worksheet). The key number is "ID" column, which is on both sheets.

I get #N/A error if I use 'Range lookup' =0 or False.
I get wrong data, if the 'Range lookup' is empty.

I have checked many possible error reasons: sorting, length of Lookup value

I've inserted formula in Assets (worksheet) on P2.
I would like top match the whole Tasks worksheet and paste it to Assets.

Anyone can help me with that?

Thanks
Cheers.
Ela

The Named Range, opgaveliste, should be:

Formula:

Please Login or Register  to view this content.

Regards, TMS

Perhaps replace commas in the formula with semi-colons ( ; ) ? ( European regional settings) ?

EDIT both ranges should be the same length I think C1:C145 in the formula or A2:A145

Try,

P2=IFERROR(INDEX(TASKS!$A$1:$K$145,MATCH($N2,TASKS!$C$2:$C$145,0)+1,MATCH(P$1,TASKS!$A$1:$K$1,0)),"") and drag down.

Check the attached file.

Why not just adjust the Named Range as I suggested in pot #3? Or, if the Named Range is used elsewhere, create another one.

Regards, TMS

Thanks.

But 'ID' is not a unique number/value on one worksheet (Tasks). Because there are more tasks on one asset.Se for example line 37,38 on TASKS worksheet. The 'ID' nr is the same 2827.
I can't get both values

Originally Posted by Ebalinska

I corrected it, but now I'm getting another error: #Name (#navn- in Danish)

index is INDEKS in Danish
match is SAMMENLIGN

See the updated sample workbook attached.

Note: a basic VLOOKUP or INDEX/MATCH will only return the FIRST matching record.

ok.
So is there any command, which can solve that (I mean, if there are more than one matching cell)?
Thank you all for help.

Btw. It is a great forum (it is my first post). I didn't expect so quick response and so kind people :-)

Again, it is because you are trying to use a Named Range that is inappropriate. Your original formula is:

Formula:

Please Login or Register  to view this content.

But BYGDEL refers to

Formula:

Please Login or Register  to view this content.

However, the ID is in column N. And the data to be returned, I think, is in column P. So I have created a new Named Range, BYGDEL2 which refers to

Formula:

Please Login or Register  to view this content.

And I have changed the formula to

Formula:

Please Login or Register  to view this content.

The value being searched for must be found in the first column of the lookup table/range. In this case, it is column N.

See the updated example.


Regards, TMS

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

You're welcome. Thanks for the rep.