How to Test two conditions within a vlookup formula

[wec43wec]

I have a large spreadsheet that I currently have a “vlook-up” formula that is providing me results, however I need to further test another set of conditions that require me to test within the results of the original vlookup function and I am not sure how to code this formula.

I want to say, if Sheet1 column F2 thru F500 = xxx “and” Sheet 1 column G2 thru G500 = yyy, then perform the if statement below, else “blank”. There can only be “one” match within this test.


Note: Original – vlookup

=IF(ISERROR(VLOOKUP(F20,'Sheet1'!$F$2:$P$500,2,FALSE)),"No Data",VLOOKUP(F20,'Sheet1'!$F$2:$P$500,2,FALSE))

 

[Gavona]

Have a look at the OR and AND functions =If(iserror(And(condition1,condition2)),"both conditions NOT matched",.....)
or maybe
=if(Or(isna(vlookup(....),isna(vlookup(...)),true,false)

Gavin

 

[GlennUK]

You could use a SUMPRODUCT to work out the row number where the 2 conditions are true, and then INDEX into whatever column you wanted results from.

For example:

=IF(SUMPRODUCT(--(F2=Sheet1!$F$2:$F$500)*(G2=Sheet1!$G$2:$G$500))>1,"more than one!",INDEX(Sheet1!$H$1:$H$500,SUMPRODUCT(--(F2=Sheet1!$F$2:$F$500)*(G2=Sheet1!$G$2:$G$500)*ROW(Sheet1!$F$2:$F$500))))

this tests to make sure only one unique result is present, and then indexes into column H of Sheet1 at the point where there are matches in columns F and G.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[kjv1611]

There's a mention of using array formulas with VLoookup at this website:

http://www.mvps.org/dmcritchie/excel/vlookup.htm

Not sure if it would give you what you need, but it sounded interesting.

--

"If to err is human, then I must be some kind of human!" -Me