how to use vlookup?

Question

我記得用vlookup 時資料是要排序的....但如果同時要2 個column 排序是沒可能的..如

A Date info
1 2006/5/1 c
1 2006/5/2 c
1 2006/5/3 a
2 2006/5/1 a
2 2006/5/2 b
2 2006/5/3 b
3 2006/5/1 a
3 2006/5/2 c
3 2006/5/3 a
3 2006/5/1 c

how to show info when A=2 , Date=2006/5/2 by using vlookup or lookup,, answer is b

r_shum

your answer is very conplicated, can it be more straight forward. i dont want insert one more column.

Answer 1

You should use Match function instead of vlookup function.

Formula as follows:
Assume you input 2 at cell D1, 200/5/2 at cell E1, your answer shows at cell F1.
In cell F1, formula would be =INDEX(C2:C20,MATCH(1,(A2:A20=D1)*(B2:B20=E1),0))

The difference between vlookup, match, index as belows:

VLOOKUP allows you to find an entry in a list and return a value that
is a given number of columns away from that entry. This can be a text
string or numeric value.

MATCH returns an integer number of the row within a range that matches
the entry in question.

You can next a MATCH function within an INDEX function and perform an
operation very similar to a VLOOKUP, because the INDEX function works
on a grid-type array of data.

An other important difference between vlookup and index&match is that when using vlookup the look-up value needs to be in the leftmost column.

Index&match doe not require that.

ie vlookup:

Col A Col B Col C
1 A HI
2 B Bye
3 C Yes
4 D No

If your lookup value was in Col B (lets say "B"), you could not look up values form Col A ("2")

Answer 2

VLOOKUP 示範: https://www.youtube.com/watch?v=BVaUbESpn54
希望幫到你

Answer 3

有問題想人解答,..唔洗重複發問既,...
問得多唔代表有人答嘛,..

要求1格既,陣列公式囉,參考,.
http://hk.geocities.com/snam_hk/00000021.xls　　　Sheet2

Answer 4

I don't know the straight forward method. But I usually do the following:
1. Insert a colume between B & C
2. type formula at the new column =CONCATENATE(A2," ",TEXT(B2,"YYYY/MM/DD"))
3. copy the formula to whole column
4. for the anwer cell, type

=VLOOKUP(CONCATENATE(xxx," ",TEXT(yyy,"YYYY/MM/DD")),C1:D10,2,FALSE)
where
xxx is address of 2
yyy is address of 2006/5/2