help on excel function

E

ecwj85

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
 
R

Rob Hick

ecwj85 said:
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
Click to expand...

Search Excel Help for VLOOKUP
 
S

shail

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
 
E

ecwj85

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

For example, worksheet 1,table one contains infos about numbers o
number of stocks by years and blocks. at worksheet 2, when i keyed i
year 1, block 2, i wan to know what is the value/number of stock b
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 dat
like year 1 to get one value and not year + block ==> value.

Hope u all get what i mean
 
S

shail

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,vlookup(A1,sheet1!A1:D10,3,false))

This will be an upgraded version of the previous formulae.


Hope this works fine for you

Thanks

Shail
 
Top