Excel - VLOOKUP question

[ffonseca]

I'm trying to match the information on columsn A & B with columns E & F, then retrive the information on column C, and populate next to the names on clumns A and B. See the screen shot below. I'm using a VLOOKUP formula but I keep getting the error # message. Any help would be appreciated. Thank you.

Copy of New List.xls
	A	B	C	D	E	F	G
1	firstname	lastname	spendingcode		firstname	lastname	limitcode
2	Adrienne	Gantt	J		Adrienne	Gantt	#VALUE!
3	Aixa	Nieves	L		Alan	Dorhoffer
current users

 

[Aladin Akyurek]

G2:

=INDEX($C$2:$C$3,MATCH(1,IF($A$2:$A$3=E2,IF($B$2:$B$3=F2,1)),0))

which needs to be confirmed with control+shift+enter (not just with enter) then copied down.

 

[u742884]

Your screen shot did not come through. About all I can make out is this:
VLOOKUP($E$2:$F$2,$A$2:$C$2,3,FALSE)

There is definitely a problem. You can only lookup off of one cell so the first part $E$2:$F$2 will not work. What you need to do is add a column that concatenates your values into one thing to lookup. Insert a column before A (I'll call it aa for my example to still be able to refer to your existing columns). Then change your formula like so:

=vlookup(e2&F2,aa2:c2,4,false)

 

[ffonseca]

Aladin,
You're the best man. I owe you a beer!

 

[ffonseca]

Aladin,
When I copied down the suggested formula I get the following error #N/A. My list has about 800 rows. Any suggestions? Thanks is advance.

 

[Aladin Akyurek]

Aladin,
When I copied down the suggested formula I get the following error #N/A. My list has about 800 rows. Any suggestions? Thanks is advance.

When there is no match, the result will be #N/A. Are you asking how to suppress such results?

 

[ffonseca]

Actually the entire column is returning with the error, and I know that there is more than one match. I thought I would be able to copy down and the formula would adjust to the next entry.

 

[Aladin Akyurek]

Actually the entire column is returning with the error, and I know that there is more than one match. I thought I would be able to copy down and the formula would adjust to the next entry.

The first formula entry needs to be confirmed with control+shift+enter. After this action, you should see { and } enclosing the formula. If that's the case, you can simply drag down the formula for the other lookup values.

If you still get #N/A when you shouldn't, then you probably have stray spaces around etries. You can remove them by running the free ASAP Utilities from asap-utilities.com.