can anyone teach me how can i lookup for 2 separate data from different
table? for example in table A, i use data year '1' and type 'a' to look
for a value in another table who got different year,different type and
value. The main point is to search for the value who is equal to year
1 type a. The other table, table b would have data like year 1 to 5,
type a, has value of 5.5 , year 6 to 10, has value of 7.1
thanks


--
ecwj85
------------------------------------------------------------------------
ecwj85's Profile: http://www.excelforum.com/member.php...o&userid=37703
View this thread: http://www.excelforum.com/showthread...hreadid=572973

ecwj85 wrote:
can anyone teach me how can i lookup for 2 separate data from different
table? for example in table A, i use data year '1' and type 'a' to look
for a value in another table who got different year,different type and
value. The main point is to search for the value who is equal to year
1 type a. The other table, table b would have data like year 1 to 5,
type a, has value of 5.5 , year 6 to 10, has value of 7.1
thanks


--
ecwj85
------------------------------------------------------------------------
ecwj85's Profile: http://www.excelforum.com/member.php...o&userid=37703
View this thread: http://www.excelforum.com/showthread...hreadid=572973

Search Excel Help for VLOOKUP

you need to elaborate.


"ecwj85" wrote in
message ...

can anyone teach me how can i lookup for 2 separate data from different
table? for example in table A, i use data year '1' and type 'a' to look
for a value in another table who got different year,different type and
value. The main point is to search for the value who is equal to year
1 type a. The other table, table b would have data like year 1 to 5,
type a, has value of 5.5 , year 6 to 10, has value of 7.1
thanks


--
ecwj85
------------------------------------------------------------------------
ecwj85's Profile:
http://www.excelforum.com/member.php...o&userid=37703
View this thread: http://www.excelforum.com/showthread...hreadid=572973

Hi,

Can you elaborate your question? Make it in tabular form.

You might use it this way, if I understood it correctly till now....

=sumproduct((sheet1!A1:A10="data year 1")*(sheet2!A1:A10="Type
a")*(B1:C10))

something like this

But do elaborate your question again.


Thanks,

Shail


ecwj85 wrote:
can anyone teach me how can i lookup for 2 separate data from different
table? for example in table A, i use data year '1' and type 'a' to look
for a value in another table who got different year,different type and
value. The main point is to search for the value who is equal to year
1 type a. The other table, table b would have data like year 1 to 5,
type a, has value of 5.5 , year 6 to 10, has value of 7.1
thanks


--
ecwj85
------------------------------------------------------------------------
ecwj85's Profile: http://www.excelforum.com/member.php...o&userid=37703
View this thread: http://www.excelforum.com/showthread...hreadid=572973

erm..what i really mean is..from first worksheet, where all the datas
are key in into a table. and on the other hand, the second worksheet
is where i need to get the data from the first worksheet by using 2
data to get one value from worksheet, table one.

For example, worksheet 1,table one contains infos about numbers of
number of stocks by years and blocks. at worksheet 2, when i keyed in
year 1, block 2, i wan to know what is the value/number of stock by
getting the data from worksheet 1, table 1.

my main question is how and what function can i use to get the value.
im trying vlookup function but the function can only compare one data
like year 1 to get one value and not year + block == value.

Hope u all get what i mean.


--
ecwj85
------------------------------------------------------------------------
ecwj85's Profile: http://www.excelforum.com/member.php...o&userid=37703
View this thread: http://www.excelforum.com/showthread...hreadid=572973

As far I have understood, you want the VLOOKUP to be dynamic.

If so, then...

For example,
Assuming your data is on sheet 1 and you want to search from sheet 2.
Your data range is A1:D10 at sheet 1. You want the value to appear is
at column 3 from the leftmost data of the range (i e., C), so

Over sheet 2
1. At A1 key-in the value based upon you want to search
2. At A2 write down the formulae as

=vlookup(A1,sheet1!A1:D10,3,false)

Whenever you want to search put the value in A1 and you will get the
respective value at A2.

=if(iserror(vlookup(A1,sheet1!A1:D10,3,false)),0,v lookup(A1,sheet1!A1:D10,3,false))

This will be an upgraded version of the previous formulae.


Hope this works fine for you

Thanks

Shail


ecwj85 wrote:
erm..what i really mean is..from first worksheet, where all the datas
are key in into a table. and on the other hand, the second worksheet
is where i need to get the data from the first worksheet by using 2
data to get one value from worksheet, table one.

For example, worksheet 1,table one contains infos about numbers of
number of stocks by years and blocks. at worksheet 2, when i keyed in
year 1, block 2, i wan to know what is the value/number of stock by
getting the data from worksheet 1, table 1.

my main question is how and what function can i use to get the value.
im trying vlookup function but the function can only compare one data
like year 1 to get one value and not year + block == value.

Hope u all get what i mean.


--
ecwj85
------------------------------------------------------------------------
ecwj85's Profile: http://www.excelforum.com/member.php...o&userid=37703
View this thread: http://www.excelforum.com/showthread...hreadid=572973