Excel lookup with multiple conditions! (extended vlookup)

Example of using a lookup function with multiple conditions. Formula INDEX and MATCH are used for this.

Category:

Education

Tags:

• excel
• 2007
• 2010
• vlookup
• function
• multiple
• condition
• index
• match
• Microsoft Excel
• Lookup
• Software
• Office
• Software Tutorial
• Data
• excel 2007
• Microsoft Office

[Psychotec]

erickoh215 no, I used another method to reach this goal. There is an index and match formula which looks similar to this one where you have do define 1 as the lookup value indeed. But in this case Im just calculating the number of rows for the return value. So for example Match(1;1*4) after calculation gives me 4 and will take the fourth result. I do hope you will understand my explaination. Otherwise feel free to comment again. Thanks for watching!!

[broomvertigo]

@Psychotec great start to my issue, BUT i need HELP!!!

I have 2 worksheets within 1 file. There are 10 columns (identical fields in BOTH worksheets)

I want to compare column C in wrksht 1, to column C in wrksht 2

IF 2 cells are identical between the 2 worksheets, I want to extrapolate the email adres to a 3rd wrksht

MY ROUGH LOGIC IS BELOW

=1c = 2c then DISPLAY

STEP 2 Macro -- this is a more advanced formula for comparing MULTIPLE columns

=1(x+y+z) = 2(x y z), then DISPLAY

[Psychotec]

@broomvertigo

I have another video (see my channel: Excel compare 2 ranges and save lots of work!) where I explain how to compare (multiple) ranges, based on that you can simply display for example by colors or by a macro. I also think it would be way faster to achieve what you want.

[Psychotec]

@latasha66:

yes, you can use the * as a conditional separator. And why the braces? It is because this is an array formula, in this case representing 2 columns, in yours 3. If you do not enter the braces, it would not be an array, so the formula would not work.

Hope you have enough info now, otherwise just ask :)

[erickoh215]

excellent video!

but what does the 1 in MATCH (1;(A2:A17=F1)*(B2:B17=F2),0), isn't it the lookup value? and shouldn't be a fixed value?

[latasha66]

also... whats with the braces at the end vs. no braces? What happens if you hit ENTER and do not add the braces?

[latasha66]

This is exactly what I was looking for. Can this formula be expanded to match 3 criteria instead of two? My guess would be (assuming a third columb X and F3 field) :

{=index(C2:C17;MATCH (1;(A2:A17=F1)*(B2:B17=F2)*(X2­:X17=F3);0))}

...not sure if the * is used as a conditional separator... I will have to experiment

Thanks!