Vlookup on multiple cells

[Guest]

=VLOOKUP(C4,'Named Fields'!AH3:AM116,6,FALSE)

Hi, and thanks in advance for your help.
I have the above formula set in a workbook.
I have sheet 1 with my main information.
I have sheet 2 with all my directives.

So, I have set up as follows:

sheet 1 contains:
cell c3= client
cell c4= project
The vlookup I have entered in cell c5 is to return a job number based on the
project in cell c4 which is listed on sheet2.
Problem is, I have multiple projects named the same. They're under different
clients.
I found a post that said I could use:

=vlookup(C3&C4,'Named Fields'!AH3:AM116,6,FALSE)

But that didn't work.

Is there any way to have vlookup find what's in cell C3, then proceed to
cell C4 to returns the results in the 6th column?

Thanks,

Debbie

 

[Guest]

Hi Debbie,

if on the column h you have figures, try to use sumproduct as

sumproduct(--(c7:c20="client")*(d7:d20="project"),(h7:h20))

hope this helps
regards from Brazil
Marcelo

"Debbie" escreveu:

 

[Franz Verga]

Nel post *Marcelo* ha scritto:

Hi Debbie,

if on the column h you have figures, try to use sumproduct as

sumproduct(--(c7:c20="client")*(d7:d20="project"),(h7:h20))

Hi Marcelo,

In this case you don't need "--". You can use sumproduct function in may
ways:

sumproduct(--(c7:c20="client"),--(d7:d20="project"),(h7:h20))

or

sumproduct((c7:c20="client")*(d7:d20="project"),(h7:h20))

or also

sumproduct((c7:c20="client")*(d7:d20="project")*(h7:h20))

But last one could produce errors if (h7:h20) contain a single text entry.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Marcelo and Franz,

Thanks but these didn't help.
Any other suggestions?

 

[Franz Verga]

Nel post *Debbie* ha scritto:

Marcelo and Franz,

Thanks but these didn't help.
Any other suggestions?

Why didn't help? What is the result?


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

I got #Value when I used that code.

Nel post *Debbie* ha scritto:


Why didn't help? What is the result?


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

Nel post *Debbie* ha scritto:

I got #Value when I used that code.

Maybe it could be better if you post some data or if you could post a small
example file on www.savefile.com


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Franz,

I uploaded the file.
PID 147340
Not sure what you need.
Or you could e-mail me at (e-mail address removed)
Thanks for all your help.

Debbie

 

[Franz Verga]

Nel post *Debbie* ha scritto:

Franz,

I uploaded the file.
PID 147340
Not sure what you need.
Or you could e-mail me at (e-mail address removed)
Thanks for all your help.

Debbie

Hi Debbie,
the number you posted doesn't permit to reach the file, you should post,
instead the link to the file provided from www.savefile.com.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Franz,

I don't see a way to "post" my file.
However, here's the full link.
http://www.savefile.com/projects.php?pid=147340
Then it can be downloaded.
If this doesn't work for you, please tell me how to "post" and I'll take
care of it.

Thanks,

Debbie

 

[Franz Verga]

Nel post *Debbie* ha scritto:

Franz,

I don't see a way to "post" my file.

I intended to upload...

Sorry, English it's no my mother language, so sometimes I use the wrong
words...

However, here's the full link.
http://www.savefile.com/projects.php?pid=147340
Then it can be downloaded.
If this doesn't work for you, please tell me how to "post" and I'll
take care of it.

I fixed the file for you.

To use a formula like

=VLOOKUP(C3&C4,'Named Fields'!AH3:AM116,6,FALSE)

also the first column of your table, i.e. column AH in the formula above,
*must* be a concatenation of the two fields you are looking for.

Here is the link were you can download your file modified:

http://www.savefile.com/files/5666733


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

Nel post *Franz Verga* ha scritto:

Nel post *Debbie* ha scritto:


I intended to upload...

Sorry, English it's no my mother language, so sometimes I use the
wrong words...


I fixed the file for you.

To use a formula like

=VLOOKUP(C3&C4,'Named Fields'!AH3:AM116,6,FALSE)

also the first column of your table, i.e. column AH in the formula
above, *must* be a concatenation of the two fields you are looking
for.
Here is the link were you can download your file modified:

http://www.savefile.com/files/5666733

I thik also you should "incapsulate" your VLOOKUP formulas inside an IF
function, to prevent a display of #N/A errors:

=IF(ISNA(VLOOKUP(....),"",VLOOKUP(...))

In this way way your VLOOKUP should return a #N/A, the cell will be blank.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Franz,

I've tried to download the file but it keeps kicking me to a "page not found".

 

[Franz Verga]

Nel post *Debbie* ha scritto:

Franz,

I've tried to download the file but it keeps kicking me to a "page
not found".

I tried to upload the file again, but I receive an error of "page not
found".

I suspect some problem with the servers of site, so try to download the file
later.

Anyway, I explained before how to set your table to work with this kind of
VLOOKUP formula:

=VLOOKUP(C3&C4,'Named Fields'!AH3:AM116,6,FALSE)

You have to set your table, here 'Named Fields'!AH3:AM116, with an extra
column, so the table becomes 'Named Fields'!AG3:AN116; you have also to add
1 to your results column, here from 6 to 7, so the formula becomes:

=VLOOKUP(C3&C4,'Named Fields'!AG3:AN116,7,FALSE)

The new extra column *must* be a concatenation of the same fields, and in
the same order, than C3 and C4.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Franz,

I did what you suggested.
I placed the concatenate at the beginning.
Works perfectly!!!

Thank you so much for you time and patience.

Debbie

 

[Franz Verga]

Nel post *Debbie* ha scritto:

Franz,

I did what you suggested.
I placed the concatenate at the beginning.
Works perfectly!!!


Thank you so much for you time and patience.

Debbie

You're welcome.