Send cell value as a parameter to use it as a value criteria for vlookup formula

Question

I have the following as target:

In another workbook, I have as a data source this table:

So, using VLOOKUP formula I'm retrieving the values that I want for the cell just with formulas like that:

=CONSULTAV("user1";login.xlsx!Tabla1[#Datos];2;FALSO

CONSULTAV is the spanish formula for VLOOKUP, so I guess in english (just for better understanding as this is a english website) should be something like:

=VLOOKUP("user1";login.xlsx!Table1[#Data];2;FALSE

I want to type in the cell a simpler formula , something like:

=FindValue("user1")

So that formula calls the VLOOKUP formula and just uses the value as first argument for the VLOOKUP formula for searching the value.

Are you familiar with vba? You could create a custom Function. support.office.com/en-us/article/… — Magnetron
hopefully you are not using it like it is, because even the sheet is (very) hidden, you could get all the values just using the immerdiate window in vba. knowing the table/sheet name from your formula, you not even need vba to get all users and passwords... — Dirk Reichel
I'm familiar with vba but is not an option here, the decision is not in my hand — Pablo
@DirkReichel as I said before the decision is not in my hand and this is for a very early test enviroment so the visibility or security is not a concern. With naming, yo mean assign a name to the range you want to scan? — Pablo
You're asking for a simpler, native formula which calls VLOOKUP, but is given only one argument? This is impossible, because 2 of the other 3 arguments to VLOOKUP are required, and unless you can hard-code those in to a UDF, this is only possible by combining other native formulas, which would be more complicated than the existing VLOOKUP. — David Zemens

Unknown Unknown · Accepted Answer · 2016-10-05T16:40:30

Assuming the following data layout,

Select B3 and then create a defined name with the following parameters,

That formula is,

=VLOOKUP("password"&RIGHT(Sheet4!B2, 1), Table1[#All], 2, FALSE)

Now use =FindPassword in B3.

This method will always find the password associated with the user in the cell directly above it.

Send cell value as a parameter to use it as a value criteria for vlookup formula

1 Answers