IF vlookup return "value"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i want the cell to check 3 worksheets from another workbook and return S, FI
or PI depending on which sheet it is on, have the following but know its
wrong, cannot fine tune. thanks anyone

=IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents and
Settings\Gillian Mason\My Documents\MDA\[copy 2Share
Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My Documents\MDA\[copy
2Share Recommendations.xls]Property & Infrastructure'!$B$10:$D$413,PI,))))))
 
Hi!

Yuck!

Maybe something like this:

=IF(COUNTIF('Path\[copy 2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path\[copy
2share recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path\[copy 2share
recommendations.xls]Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))

Replace "Path" with:

'C:\Documents and Settings\Gillian Mason\My Documents\MDA

Biff
 
Thanks Biff. now it seems to work in that it doesnt ask for more info, but it
is blank when i know the value in A4 is on the first worksheet of the other
doc.
what say you

=IF(COUNTIF('‘C:\Documents and Settings\Gillian Mason\My Documents\MDA\[copy
2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('‘C:\Documents
and Settings\Gillian Mason\My Documents\MDA\[copy 2share
recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('‘C:\Documents and
Settings\Gillian Mason\My Documents\MDA\[copy 2share
recommendations.xls]Property & Infrastructure'!$B$10:$B$413,A4),"PI","None")))


Biff said:
Hi!

Yuck!

Maybe something like this:

=IF(COUNTIF('Path\[copy 2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path\[copy
2share recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path\[copy 2share
recommendations.xls]Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))

Replace "Path" with:

'C:\Documents and Settings\Gillian Mason\My Documents\MDA

Biff

Micayla Bergen said:
i want the cell to check 3 worksheets from another workbook and return S,
FI
or PI depending on which sheet it is on, have the following but know its
wrong, cannot fine tune. thanks anyone

=IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents and
Settings\Gillian Mason\My Documents\MDA\[copy 2Share
Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy
2Share Recommendations.xls]Property &
Infrastructure'!$B$10:$D$413,PI,))))))
Click to expand...
Click to expand...
 
Hi!

What do you mean by: "it doesnt ask for more info" ?
it is blank when i know the value in A4
Click to expand...

What is blank? The result of the formula? It can't be. The only possible
values that it can return are: S, FI, PI or NONE (or an error if you have
errors in any of those ranges).

Biff

Micayla Bergen said:
Thanks Biff. now it seems to work in that it doesnt ask for more info, but
it
is blank when i know the value in A4 is on the first worksheet of the
other
doc.
what say you

=IF(COUNTIF(''C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy
2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF(''C:\Documents
and Settings\Gillian Mason\My Documents\MDA\[copy 2share
recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF(''C:\Documents and
Settings\Gillian Mason\My Documents\MDA\[copy 2share
recommendations.xls]Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))


Biff said:
Hi!

Yuck!

Maybe something like this:

=IF(COUNTIF('Path\[copy 2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path\[copy
2share recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path\[copy 2share
recommendations.xls]Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))

Replace "Path" with:

'C:\Documents and Settings\Gillian Mason\My Documents\MDA

Biff

message
i want the cell to check 3 worksheets from another workbook and return
S,
FI
or PI depending on which sheet it is on, have the following but know
its
wrong, cannot fine tune. thanks anyone

=IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents
and
Settings\Gillian Mason\My Documents\MDA\[copy 2Share
Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy
2Share Recommendations.xls]Property &
Infrastructure'!$B$10:$D$413,PI,))))))
Click to expand...
Click to expand...
Click to expand...
 
thats exactly right! it returns nothing, whereas it should return something.
what i mean by ask for more info is if the formula was incomplete or
incorrect it usually has a dialogue box to that effect.

Biff said:
Hi!

What do you mean by: "it doesnt ask for more info" ?
it is blank when i know the value in A4
Click to expand...

What is blank? The result of the formula? It can't be. The only possible
values that it can return are: S, FI, PI or NONE (or an error if you have
errors in any of those ranges).

Biff

Micayla Bergen said:
Thanks Biff. now it seems to work in that it doesnt ask for more info, but
it
is blank when i know the value in A4 is on the first worksheet of the
other
doc.
what say you

=IF(COUNTIF(''C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy
2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF(''C:\Documents
and Settings\Gillian Mason\My Documents\MDA\[copy 2share
recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF(''C:\Documents and
Settings\Gillian Mason\My Documents\MDA\[copy 2share
recommendations.xls]Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))


Biff said:
Hi!

Yuck!

Maybe something like this:

=IF(COUNTIF('Path\[copy 2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path\[copy
2share recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path\[copy 2share
recommendations.xls]Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))

Replace "Path" with:

'C:\Documents and Settings\Gillian Mason\My Documents\MDA

Biff

message
i want the cell to check 3 worksheets from another workbook and return
S,
FI
or PI depending on which sheet it is on, have the following but know
its
wrong, cannot fine tune. thanks anyone

=IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents
and
Settings\Gillian Mason\My Documents\MDA\[copy 2Share
Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy
2Share Recommendations.xls]Property &
Infrastructure'!$B$10:$D$413,PI,))))))
Click to expand...
Click to expand...
Click to expand...
 
sorry, i had my text in white. it is returning a value error.

Biff said:
Hi!

What do you mean by: "it doesnt ask for more info" ?
it is blank when i know the value in A4
Click to expand...

What is blank? The result of the formula? It can't be. The only possible
values that it can return are: S, FI, PI or NONE (or an error if you have
errors in any of those ranges).

Biff

Micayla Bergen said:
Thanks Biff. now it seems to work in that it doesnt ask for more info, but
it
is blank when i know the value in A4 is on the first worksheet of the
other
doc.
what say you

=IF(COUNTIF(''C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy
2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF(''C:\Documents
and Settings\Gillian Mason\My Documents\MDA\[copy 2share
recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF(''C:\Documents and
Settings\Gillian Mason\My Documents\MDA\[copy 2share
recommendations.xls]Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))


Biff said:
Hi!

Yuck!

Maybe something like this:

=IF(COUNTIF('Path\[copy 2share
recommendations.xls]Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path\[copy
2share recommendations.xls]Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path\[copy 2share
recommendations.xls]Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))

Replace "Path" with:

'C:\Documents and Settings\Gillian Mason\My Documents\MDA

Biff

message
i want the cell to check 3 worksheets from another workbook and return
S,
FI
or PI depending on which sheet it is on, have the following but know
its
wrong, cannot fine tune. thanks anyone

=IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$D$413,S,IF(VLOOKUP(A4,'C:\Documents
and
Settings\Gillian Mason\My Documents\MDA\[copy 2Share
Recommendations.xls]Income Securities'!$B$10:$D$413,FI,
IF(VLOOKUP(A4,'C:\Documents and Settings\Gillian Mason\My
Documents\MDA\[copy
2Share Recommendations.xls]Property &
Infrastructure'!$B$10:$D$413,PI,))))))
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup 3 columns all return same 4
addon to equation 6
return value from other spreadsheet 9
Vlookup not returning correct value 4
VLOOKUP Function to return values in multipal Columns 6
VLOOKUP and #N/A 2
VLOOKUP and return formula 1
Vlookup-Doesn't return values under 1 1

Back
Top