VLOOKUP MULTIPLE COLUMNS

[TUhl]

With the knowlege that I have of Excel, VLOOKUP is the best function I know of to look up and match to other data in a workbook. If there is another way to do this please let me know.

I need to use 2 columns in a data array to match in order to be supplied with the correct item number that is also in the data array (in a different column). This is what I have tried:

=VLOOKUP(G2:H2,data array,1,false)

This is the part I am having trouble with

Help?!

Thank you

Toni

 

[Diffy]

I am a little confused. what is in your data array? And why would you want to return column 1? that would just return the value of G2...

You need to set it up so Data Array includes the first two columns are the two respective values you want to match in G2:H2. Then 1 should be changed to the column number of the data you wish to return.

If you could post what values are in G2:H2 and also what data is in your data array along with more explanation.

 

[TUhl]

I have 2 columns (F2:G2) that describe an item. When those two match I want it to return one specific item number. I moved my data array around so that now I have Item Size - Grade - Item # so now my formula looks like this:

=VLOOKUP(F2:G2,'ITEM #S'!A2:C10,3,FALSE)

Does this make sense?

 

[Oaktree]

F3=INDEX(C2:C5,MATCH(1,(A2:A5=F1)*(B2:B5=F2),0)), which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar)

Book17
	A	B	C	D	E	F
1	First Name	Last Name	Value		Lookup First	Scrappy
2	Scooby	Doo	1		Lookup Last	Doo
3	Scrappy	McGee	2		Returned	3
4	Scrappy	Doo	3
5	Pappa	Smurf	4
Sheet1

 

[TUhl]

Oaktree --

I'm sorry but I don't understand the formula that you gave me. Can you give me more explination?

thanks

 

[Oaktree]

The formula I suggested matches F1 against column A and F2 against column B. It returns the (first) value from column C where the intersection occurs.

Since Row 4 matches both "Scrappy" in column A and "Doo" in column B, 3, the value from C4, is returned.

 

[TUhl]

Ok, that is why it wasn't working, that isn't what I am trying to do. I am trying to do a basic VLOOKUP function but instead of a theoretical A1 matching something in the data array I want it to look at A1:B1 and match both in the data array and return with one number (from the 3rd column in the data array).

Does that make sense? Is it possible?

 

[Oaktree]

Please give us an example of the data and the results of what you're trying to do (see the link to "Download Colo's HTML Maker..." to post a sample of your sheet as in my prior post)

 

[TUhl]

I downloaded the HTML maker, I opened it, enabled macros, and now i can't find it, it says that it is open when i try to reopen it. I'm sorry...obviously i'm new at this...

 

[Oaktree]

See:

http://www.mrexcel.com/board2/viewtopic.php?t=92622