Multiple value VLOOKUP? is this the right approach?

[ibddude]

I have two worksheets and what I'd like to do is based on 3 unigue cell values say, A1, B1, C1, I want to go to the other worksheet, find the row which matches these values, say Sheet1A1 = Sheet2V45, Sheet1B1 = Sheet2 X45, etc... and return some other cell values that I can use in Sheet1. I know it's a VLOOKUP, but not sure the best way to approach where I have to match 3 values.

 

[T. Valko]

I have two worksheets and what I'd like to do is based on 3 unigue cell values say, A1, B1, C1, I want to go to the other worksheet, find the row which matches these values, say Sheet1A1 = Sheet2V45, Sheet1B1 = Sheet2 X45, etc... and return some other cell values that I can use in Sheet1. I know it's a VLOOKUP, but not sure the best way to approach where I have to match 3 values.

Try something like this...

Book1
	A	B	C	D
1	X	Y	Z	Yes
2	_	_	_	_
3	_	_	_	_
4	_	_	_	_
5	A	S	D	No
6	F	G	H	Not
7	H	J	K	Nope
8	X	Y	Z	Yes
9	C	F	Z	Nah
10	R	T	Y	No Way
Sheet1

This array formula** entered in D1:

=INDEX(D5:D10,MATCH(1,IF(A5:A10=A1,IF(B5:B10=B1,IF(C5:C10=C1,1))),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Peter_SSs]

A non-array alternative, for the same layout as post #2, is

=INDEX($D$5:$D$10, MATCH(A1&"|"&B1&"|"&C1, INDEX($A$5:$A$10&"|"&$B$5:$B$10&"|"&$C$5:$C$10, 0), 0))

 

[ibddude]

I need to study these in more detail but I hope I explained my need in enough detail.

On sheet A I want to go to each row and take the value on cell B*, F*, and G* and then go to sheet B and find the row where those values match (I know which columns on sheet 2). Once I have a match, I want to return values from others cells on sheet 2 into black cells on the same row on sheet2

I hope this explanation is better than my initial. I have not used INDEX so I need to study this to see if this does what I need to do.

thanks

 

[Peter_SSs]

I need to study these in more detail but I hope I explained my need in enough detail.

On sheet A I want to go to each row and take the value on cell B*, F*, and G* and then go to sheet B and find the row where those values match (I know which columns on sheet 2). Once I have a match, I want to return values from others cells on sheet 2 into black cells on the same row on sheet2
I hope this explanation is better than my initial. I have not used INDEX so I need to study this to see if this does what I need to do.

thanks

I think both basic approaches should work for you. However, the blue bit above seems to indicate that you want to return values from multiple columns of the matching row in the second sheet. To achieve that, a bit of tweaking of both formulas will be required in relation to where $ signs should be used.

If you need further help please

- advise the actual sheet names (you've use sheet A, sheet B and sheet 2 in your description above) and identify which one contains th table of data and which one is to hold these formulas.

- confirm that the values to be matched are in column B, F and G of the formula sheet (starting in row 2?)

- advise which columns in the other sheet to look for these values. You say "I know which columns on sheet 2", but we don't know.

- advise which columns have to be fetched back from the table sheet and which column on the formula sheet each one should be fetched back to.