excel in 365 using =VERT.ZOEKEN(C17;T2:U25;2;ONWAAR)

[ellermanstraat21]

goodmorning and best wishes.

I have a problem with the =VERT.ZOEKEN(C17;T2:U25;2;ONWAAR) function where I want to use this formula in column C from top to bottom of my excel sheet without changing T2:U25. I tried to copy the formula to the next cell but then not only C17 changes to C18 but the T2:U25 changes also from T2:U25 to T3:U26 and that is not the plan. Can someone let me know how to implement this in a correct way?

[#2 · Accepted solution]

@ellermanstraat21 

=VERT.ZOEKEN(C17;$T$2:$U$25;2;ONWAAR)

Schakelen tussen relatieve, absolute en gemengde verwijzingen

Als u bijvoorbeeld de formule =B4*C4 kopieert van cel D4 naar D5, wordt de formule in D5 met één kolom naar rechts aangepast en wordt deze =B5*C5. Als u de oorspronkelijke celverwijzing in dit voorbeeld wilt behouden wanneer u deze kopieert, maakt u de celverwijzing absoluut door de kolommen (B en C) en rij (2) met een dollarteken($)te vervangen. Wanneer u vervolgens de formule =$B$4*$C$4 van D4 naar D5 kopieert, blijft de formule precies hetzelfde.

Hope I was able to help you with this info.

NikolinoDE

I know I don't know anything (Socrates)

Was the answer useful? Mark them as helpful!

This will help all forum participants.

[#3]

Dank voor uw antwoord. Weet u misschien ook hoe ik een lege cell kan laten zien als in T2:U25 als C17 nog geen waarde heeft? Momenteel krijg ik #N/B zolang C geen waarde heeft

[#4]

@ellermanstraat21 

=(ALS.FOUT(VERT.ZOEKEN(C17;$T$2:$U$25;2;ONWAAR);""))

To be sure how the error arises (if the formula does not lead to the solution you would need the file (of course without sensitive data), so here are some options that may be available in the worksheet.

It could be text even though it looks like a number.

You can't achieve anything with formatting, as a number format can only be applied to numbers, a number format doesn't matter to text!

There are several ways to convert your text to numbers. The simplest is to multiply the text by 1. To do this, you write a 1 in any free cell. You copy it (e.g. Ctrl + C), then mark the entire area of your "text numbers", with Edit / Insert Values / Multiply you can then achieve what you want. Then do not close with Enter, but the still active marquee with Esc. Clear.

then =VERT.ZOEKEN (is VLOOKUP in English) works too.

By the way, if you have formatted the area with standard, you can recognize the text by the fact that e is left-justified, a number would be right-justified.

Here is a link with further possible errors.

De fout #N/B corrigeren

Thank you for your patience and time

Nikolino

I know I don't know anything (Socrates)

[#5]

thank you for your help this works perfect for me.

[#6]

I am pleased that I was able to help you.
I also wish you have fun with excel.

[#7]

@ellermanstraat21 

Just an observation, if you are using 365 then VLOOKUP is a legacy function, replaced by XLOOKUP, which also allows the developer to select the action they wish to take if the search produces a null return (this will normally be a text string equivalent to "not found" but it could be the result of an entirely different formula).