V Look up 101

[Cadette]

Hi, I'm a newbie in terms of the exploration of the Excel Functions and would like to seek your help on how does the V-Look up function actually works. I keep hearing it from my colleagues but was not able to understand how it works.

Can you give me an insight?

I am planning to use it in one of my office projects (that is still in my drawing board though).

Thanks in advance..

 

[barry houdini]

Nice formula, shyy!

It also works with IF in place of CHOOSE

=VLOOKUP(E2,IF({1,0},$B$2:$B$4,$A$2:$A$4),2,FALSE)

 

[shyy]

didn't know that, thanks for the tidbit

 

[Jonmo1]

Nice formula, shyy!

It also works with IF in place of CHOOSE

=VLOOKUP(E2,IF({1,0},$B$2:$B$4,$A$2:$A$4),2,FALSE)

Wow, that is awesome!


Side note:
How did thisget interpraited as a Left Lookup ?

Hi Guys and Gals,

what if, the column i am to look at have the same entity, say a last name, then i would like to have the vlookup look at the next column, say first name.

To me, that sounds like a 2 criteria Vlookup, like
A1=John
B1=Doe

Then Column C is First Names and Column D is Last names.
And we want to lookup John Doe in Column C and D..

 

[shyy]

I agree, it doesn't sound like a LEFT VLOOKUP because the OP specified the next column is the First Name. So A1 would be Doe and B1 would be John. If Doe is found in Column A, to return the value in column B using John as the lookup value. That is how I am seeing it.

 

[Cadette]

Hi Guys and gals, thanks for the inputs and apologies for creating a confusion.

the situation goes like this, i am to look for a certain information for list of persons. i have two sets of lists, one is around 470 names and the other is 670 names. The names in the 470 can also be found in the 670. the information i have are as follows: Last name, First name, information. my dilemma is this, i have to update the information of the 470 from the 670. i planned on using the last names as initial reference in finding the information. but since these are names, there are possibilities of similar surnames. i was thinking a conditional formula, of sort, may be used (not sure if i have got the concept right). but right now, i still cant come up with a formula that works...

it was like, Column A for the last names, B for First name and C for the information. the dilemma is like, there are more than two entries of Doe in column A, and
B contains John, Peter, Jack,etc. It was more like, i have a John Doe, Peter Doe and a Jack Doe in my list.

hope you can help me..

 

[shyy]

Take a look at my thread

http://www.mrexcel.com/forum/excel-...on-about-vlookup-concatenate-table-array.html

 

[Cadette]

hi, thanks for the inputs. i am starting to see a better light of this. Thanks a mil!

another question though, i am having a hard time identifying how can incorporate in my constructed formula the condition that if there are more than one entry of the surname, the formula must look at the cell next to it.

what i have, so far is this:

=index(Rawfile!D5:D641,match(update!A5,Rawfile!B5:B541,0))

i would like to have a condition statement in the MATCH part or somewhere in the formula so it will perform the condition i have indicated above.

 

[DavidCoop]

Is this what you need?

Excel 2010
	A	B	C
1	Surname	Given Name	Information
2	Doe	Peter	3
Input

Cell Formulas
Range	Formula
C2	{=INDEX(RawData!C$2:C$5,MATCH(1,IF(RawData!A$2:A$5=A2,IF(RawData!B$2:B$5=B2,1)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excel 2010
	A	B	C
1	Surname	Given Name	Information
2	Doe	John	2
3	Doe	Peter	3
4	Doe	Jack	4
RawData

 

[shyy]

If you are going to lookup numbers, suggest you use SUMIF or SUMIFS or SUMPRODUCT instead

Excel 2010
	A	B	C
1	Surname	Given Name	Information
2	Doe	John	2
3	Doe	Peter	3
4	Doe	Jack	4
5
6
7
8
9	Surname	Given Name	Information
10	Doe	Peter	3
Sheet2

Cell Formulas
Range	Formula
C10	=SUMIFS($C$2:$C$4,$A$2:$A$4,A10,$B$2:$B$4,B10)

 

[Cadette]

Hi, Thanks for the input. your formula you shared have showed me what i need... thanks again.