A
Afolabi
Please I need a vlookup formula that could look up a value from 2 different
worksheets. any help?
worksheets. any help?
T
tim m
You need to give us more information. What are you trying to lookup?
Perhaps give us an example of your actual data in each sheet?
Perhaps give us an example of your actual data in each sheet?
J
JLatham
Essentially you could put 2 separate lookups in one cell, something like
this, assumes that the criteria to use as the basis for both lookups is in
cell A1 of the sheet this is on (call it Sheet3)
=VLOOKUP(A1,Sheet1!TableRange,3,False) & " | " & VLOOKUP(A1,
Sheet2,Table2Range,7,False)
you'd get the results of the 2 lookups separated by a bar like
Mary | Lamb
presuming "Mary" and "Lamb" are results available in the tables on sheets 1
and 2. You could do other operations besides just concatenating strings,
like addition, multiplication, etc. The results of the VLOOKUP()s just get
treated like any other reference.
this, assumes that the criteria to use as the basis for both lookups is in
cell A1 of the sheet this is on (call it Sheet3)
=VLOOKUP(A1,Sheet1!TableRange,3,False) & " | " & VLOOKUP(A1,
Sheet2,Table2Range,7,False)
you'd get the results of the 2 lookups separated by a bar like
Mary | Lamb
presuming "Mary" and "Lamb" are results available in the tables on sheets 1
and 2. You could do other operations besides just concatenating strings,
like addition, multiplication, etc. The results of the VLOOKUP()s just get
treated like any other reference.
A
Afolabi
Thanks for the attempt, I actually want vlookup to look for a criteria in
the 1st worksheet, and if the criteria is not in the 1st worksheet, to look
for it in the 2nd worksheet. The criteria is present in either of the
2worksheets, and present result in a 3rd worksheet.
However, if the criteria is not in either, I want excel to return " "
the 1st worksheet, and if the criteria is not in the 1st worksheet, to look
for it in the 2nd worksheet. The criteria is present in either of the
2worksheets, and present result in a 3rd worksheet.
However, if the criteria is not in either, I want excel to return " "
A
Afolabi
Thanks for the attempt, I actually need vlookup to look up some common
criteria from EITHER of 2 different worksheets and return specified values
(in identified columns) to a 3rd worksheet.
I actually have the 1st worksheet containing COST of a couple of assets, and
the 2nd worksheet containing DEPRECIATION on the same asset. I have the 3rd
worksheet called BUDGET where I want to pull the COST or DEPRECIATION (but on
different lines)on these assets to.
Any help?
criteria from EITHER of 2 different worksheets and return specified values
(in identified columns) to a 3rd worksheet.
I actually have the 1st worksheet containing COST of a couple of assets, and
the 2nd worksheet containing DEPRECIATION on the same asset. I have the 3rd
worksheet called BUDGET where I want to pull the COST or DEPRECIATION (but on
different lines)on these assets to.
Any help?
J
JLatham
I'm going to echo tim m's request for some examples. You can use VLOOKUP()
or other lookup functions as long as you know what you want to match and
where that match can be found elsewhere. But there has to be stuff in all
locations to be matched, and with lookups like VLOOKUP() and HLOOKUP(), the
tables to match in and draw information from have to be laid out in a
particular way. Other functions like INDEX() and MATCH() and LOOKUP() suffer
from fewer restrictions.
There is nothing to say that you cannot have a 'table' on sheet one like:
ITEM1 Name $Depreciation
ITEM2 Name $Depriciation
and having a table on another sheet like
ITEM1 Name $Cost
ITEM2 Name $Cost
and then on a third sheet, perhaps in cell A1 you have
ITEM1 Name
and way down on the sheet, maybe at O49, you can have a formula like
=VLOOKUP(A1,'Sheet1!A1:B2',2) and get the Depreciation from Sheet1
and right next to it in P49 you could have
=VLOOKUP(A1,'Sheet2!A1:B2',2) and get the cost
You could put both of those inside of an IF() formula to make a choice of
which lookup to use. Referring to the example just given, lets say that in
N49 you can put a D when you want Depreciation but if anything else is in it,
you want to see the cost, then you could have something like this in O49:
=IF(N49="D",VLOOKUP(A1,'Sheet1!A1:B2',2),VLOOKUP(A1,'Sheet2!A1:B2',2))
There are many possibilities, but we have to have a clear idea of what we
have to work with here.
or other lookup functions as long as you know what you want to match and
where that match can be found elsewhere. But there has to be stuff in all
locations to be matched, and with lookups like VLOOKUP() and HLOOKUP(), the
tables to match in and draw information from have to be laid out in a
particular way. Other functions like INDEX() and MATCH() and LOOKUP() suffer
from fewer restrictions.
There is nothing to say that you cannot have a 'table' on sheet one like:
ITEM1 Name $Depreciation
ITEM2 Name $Depriciation
and having a table on another sheet like
ITEM1 Name $Cost
ITEM2 Name $Cost
and then on a third sheet, perhaps in cell A1 you have
ITEM1 Name
and way down on the sheet, maybe at O49, you can have a formula like
=VLOOKUP(A1,'Sheet1!A1:B2',2) and get the Depreciation from Sheet1
and right next to it in P49 you could have
=VLOOKUP(A1,'Sheet2!A1:B2',2) and get the cost
You could put both of those inside of an IF() formula to make a choice of
which lookup to use. Referring to the example just given, lets say that in
N49 you can put a D when you want Depreciation but if anything else is in it,
you want to see the cost, then you could have something like this in O49:
=IF(N49="D",VLOOKUP(A1,'Sheet1!A1:B2',2),VLOOKUP(A1,'Sheet2!A1:B2',2))
There are many possibilities, but we have to have a clear idea of what we
have to work with here.
R
Ragdyer
I'll assume that you're attempting to lookup a value in a datalist on
Sheet1,
And if it's not found there, then lookup the *same* value in a datalist on
Sheet2,
and where this lookup formula and the value to lookup could be situated in
Sheet3.
Datalists on Sheets 1 & 2 have similar location references.
A1 to D50,
The only difference is the column index of the value to return.
Column D from Sheet1, and Column C from Sheet2.
=IF(ISNA(MATCH(E1,Sheet1!A1:A50,0)),IF(ISNA(MATCH(E1,Sheet2!A1:A50,0)),"No
Match",VLOOKUP(E1,Sheet2!A1
50,3,0)),VLOOKUP(E1,Sheet1!A150,4,0))
Sheet1,
And if it's not found there, then lookup the *same* value in a datalist on
Sheet2,
and where this lookup formula and the value to lookup could be situated in
Sheet3.
Datalists on Sheets 1 & 2 have similar location references.
A1 to D50,
The only difference is the column index of the value to return.
Column D from Sheet1, and Column C from Sheet2.
=IF(ISNA(MATCH(E1,Sheet1!A1:A50,0)),IF(ISNA(MATCH(E1,Sheet2!A1:A50,0)),"No
Match",VLOOKUP(E1,Sheet2!A1
50,3,0)),VLOOKUP(E1,Sheet1!A150,4,0))
A
Afolabi
Yes, you got my thought, exactly what I want. the challenge now is that excel
is not matching my search criteria, I ve checked spelling, format etc but
excel returns "no match" on all the destination cells.
is not matching my search criteria, I ve checked spelling, format etc but
excel returns "no match" on all the destination cells.
R
RagDyeR
To check the veracity of your formula, manually key in the value to lookup
into E1, and then manually key in the *exact* same value into one of the
datalists.
This should force a proper return if the formula is correct.
If it does, then you know that your data is suspect.
--
Regards,
RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------
Yes, you got my thought, exactly what I want. the challenge now is that
excel
is not matching my search criteria, I ve checked spelling, format etc but
excel returns "no match" on all the destination cells.
into E1, and then manually key in the *exact* same value into one of the
datalists.
This should force a proper return if the formula is correct.
If it does, then you know that your data is suspect.
--
Regards,
RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------
Yes, you got my thought, exactly what I want. the challenge now is that
excel
is not matching my search criteria, I ve checked spelling, format etc but
excel returns "no match" on all the destination cells.
A
Afolabi
Hmm, it is not a format error, but the formula. Please help.
R
Ragdyer
Am I to assume that after manually entering identical values into the lookup
cell and at least one row of a datalist, you still received the "No Match"
return?
The formula I posted is a *tested* formula, so I know it's correct.
My OE newsreader shows the line wrap at the error message, so I don't think
that's the problem.
What is the exact formula that you are actually using?
cell and at least one row of a datalist, you still received the "No Match"
return?
The formula I posted is a *tested* formula, so I know it's correct.
My OE newsreader shows the line wrap at the error message, so I don't think
that's the problem.
What is the exact formula that you are actually using?
A
Afolabi
Pardon the late response, this is how I applied your suggested formula:
=IF(ISNA(MATCH($A946,'[Cost
Depr.xls]Costs'!$B$192:$N$279,0)),IF(ISNA(MATCH($A946,'[Cost
Depr.xls]Depr'!$B$74:$O$258,0)),"no match",VLOOKUP($A946,'[Cost
Depr.xls]Costs'!$B$192:$N$279,6,FALSE)),VLOOKUP($A946,'[Cost
Depr.xls]Depr'!$B$74:$N$258,6,FALSE))
I however did a little study of the MATCH formula, it appears the MATCH
formula is not what I need, ( I may be wrong)
On further search on this discussion group, I met the formula below which
actually met my need.
=IF(NOT(ISERROR(VLOOKUP($A946,'[Cost
Depr.xls]Costs'!$B$192:$N$279,5,FALSE))),VLOOKUP($A946,'[Cost
Depr.xls]Costs'!$B$192:$N$279,5,FALSE),IF(NOT(ISERROR(VLOOKUP($A946,'[Cost
Depr.xls]Depr'!$B$74:$N$258,5,FALSE))),VLOOKUP($A946,'[Cost
Depr.xls]Depr'!$B$74:$O$258,5,FALSE),"not here"))
I appreciate you all, and I am still ready to learn more.
=IF(ISNA(MATCH($A946,'[Cost
Depr.xls]Costs'!$B$192:$N$279,0)),IF(ISNA(MATCH($A946,'[Cost
Depr.xls]Depr'!$B$74:$O$258,0)),"no match",VLOOKUP($A946,'[Cost
Depr.xls]Costs'!$B$192:$N$279,6,FALSE)),VLOOKUP($A946,'[Cost
Depr.xls]Depr'!$B$74:$N$258,6,FALSE))
I however did a little study of the MATCH formula, it appears the MATCH
formula is not what I need, ( I may be wrong)
On further search on this discussion group, I met the formula below which
actually met my need.
=IF(NOT(ISERROR(VLOOKUP($A946,'[Cost
Depr.xls]Costs'!$B$192:$N$279,5,FALSE))),VLOOKUP($A946,'[Cost
Depr.xls]Costs'!$B$192:$N$279,5,FALSE),IF(NOT(ISERROR(VLOOKUP($A946,'[Cost
Depr.xls]Depr'!$B$74:$N$258,5,FALSE))),VLOOKUP($A946,'[Cost
Depr.xls]Depr'!$B$74:$O$258,5,FALSE),"not here"))
I appreciate you all, and I am still ready to learn more.