Function Wizard- VLookup Version: 6.3.4.2 (x64)

[FCWC1]

So Im trying to figure out why the Wizard Vlookup dosent work
I found 1 reference to what I’m encountering but though marked SOLVED was no resolutions in thread:

SOLVED: vlookup formula just remains in cell, no error

I have 2 worksheets, {same file} one with a column which has a drop down list and a 2nd worksheet with sorted data and memo type information {in the 2nd column or B column}. I’m trying to retrieve that 2nd column information. In sheet 1 the drop down list in column E works fine but when I enter a VLOOKUP formula to column F that formula just remains in the cell in a light green color. No error but also no data. This is an example of the formula entered: =VLOOKUP(E3,$‘Payee-Depost-List’.A$2:…

I have
1 book multiple sheets. In the column I want data returned
I use the Wizard to create a VLookup. Click A2 for search criterion. Move to sheet with array. Click first and then last. ($ProductFilters.A2:C431)
Then click C2 for Index ($ProductFilters.C2) and then type FALSE
Function Result - #VALUE!

The resulting formulat it generates:
=VLOOKUP(A2,$ProductFilters.A2:C431,$ProductFilters.C2,FALSE)

I have a working solution from karolus
back in 2023
=IFNA(VLOOKUP($A2,$ProductFilters.$A$2:$C$638,3,0),"")

BUT WHY isnt the Wizard working?

[Villeroy]

menu:View>Highlight Values is a toggle option.
When ON, all cells having a constant number are shown with a blue font, and cells having a formulas are green.
If your formula cells turn green, turn off menu:View>Show Formulas which is also a toggle option.
If your formula cells are shown in plain black font, your cell has been prepared to be a text cell. Remove the text formatting and enter the formula again.

[Wanderer]

@FCWC1

Then click C2 for Index ($ProductFilters.C2)

Look at the formula by karolus and you find a 3 instead of your C2.
So YOUR version tries to retrieve 3 from C2, as YOU instructed.
The “wizard” puts cell-addresses in the formula, but here you need a column index counted from the left column used for searching.

[LeroyG]

@FCWC1

=VLOOKUP(A2,$ProductFilters.A2:C431,$ProductFilters.C2,FALSE)

It must work if $ProductFilters.C2 value is a number (not text) >=1 and <=3.
But I am not sure what is the content of your spreadsheet. If C2 content is text you will get #VALUE.

“6.3.4.2”:
I think that this version is not a problem, but could not test in this PC.

[FCWC1]

LeroyG - you sir have it.
Column is text. (even tried setting it number-general) What changes need to be made if content is text?

[FCWC1]

Wandere - the working formula was not with the Wizard. This is about about the Wizard. The Wizard returns $ProductFilters.C2 for Index when you click on the first content row.

[LeroyG]

@FCWC1

What changes need to be made if content is text?

If words, it wouldn’t work.

If number as text:

• Retype it; or
• Copy (Ctrl+C), and Paste as Unformatted Text (Ctrl+Alt+Shift+V); or
• Choosse menu Data - Text to Columns, OK.

[Wanderer]

@FCWC1

This is about about the Wizard. The Wizard returns $ProductFilters.C2

I would not call this a wizard. It is a simple tool, wich inserts the Address (of the cell you clicked) at the cursor position or field). It checks neither syntax nor if your formula is right.
.
In your case you don’t need the adress (C2) but the numerical colum number counted from your first column (something like C-A+1 => 3) so for your wizard-picture: You used the wrong spell (unless your C2 containes 3). I know the correct answer, because you have shown the answer by karolus. Calc can not know this, but will follow your command, no “intelligence” involved.
.
To spell it out: Write 3 yourself, if you need it, it will not hurt. What you call a wizard will not “think” for you.

[FCWC1]

@LeroyG

ex

So the Wizard will not work IF column is actual text. IE - words?
I’ve attached a small example file.
Exmaple.ods (14.4 KB)
This has both numbers, numbers as text, and text (words).

Just trying the wizard VLookup I get either of these 3 errors: #N/A , Err:502 , #VALUE!
Doing either Cut and past or just Data Text to columns doesn’t change that.
Note on the Cut-Paste - no where does it ask/ say “Paste as Unformatted Text”.

[LeroyG]

@FCWC1

So the Wizard will not work IF column is actual text. IE - words?

Is as you wrote: it won’t work.

@FCWC1

Text to columns doesn’t change that

The cells in C column in ProductFilters sheet is formatted as text, and left aligned manually.
If do you change the cell formatting to Number - Standard, then you can do Text to Column. But don’t do it.

But the values in column C are not fitted, nor intended, to be used as the column index for the VLOOKUP function.
Just put in D2 (of Product sheet) the last formula of your question.