lookup based on one criteria & skipping blanks

**tnfire** · 02-17-2009, 01:02 PM

I'm looking for a formula to help me look up something and return a result only when one other criteria is met. I've looked at SUMPRODUCT and VLOOKUP as options but haven't been able to make them work.

Here is what I need:

I want cell E33 to look up cell B33 in 'Test Result Table' and return the 12th column of information, but only when cell C33 and 3rd column in 'Test Result Table' match. If the first result isn't a match I want the lookup to skip it and return the first available match.

I don't want to add anything, I just need a match returned based on C33 and the 3rd column being equal (cell C33 is a VLOOKUP of the 3rd column in 'Test Result Table'). Any ideas on how to get this done? It seems simple enough, but I'm stuck.

**NBVC** · 02-17-2009, 01:11 PM

Perhaps something like:

=INDEX('Test Result Table'!A1:L100,MATCH(1,('Test Result Table'!B1:B100=E33)*('Test Result Table'!C1:C100=C33),0),12)

where A1:L100 contains table on 'Test Result Table' sheet. Adjust as necessary.

The formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER.. you will see { } brackets appear around the formula.

**tnfire** · 02-17-2009, 01:54 PM

That worked very well, I think. Thank you.

I'm applying that formula to look up dates that are found in the same column on the 'Test Result Table' sheet for two different pieces of test information. I only think it's working because I don't know how to get VLOOKUP to skip blanks and return the first available number. 'Test Result Table' is a query pulling information from an Access database.

The two columns of test information I'm looking for are "Purity" and "Germ". While one test occasionally returns both of those, more often than not there will be a number assigned to the Purity column and the Germ cell right next to it will be blank, in the query. When I try to use VLOOKUP to look up the Purity, then Germ, I'll get the first values they find; a real value for Purity and then the blank that's next to it or a '0' for Germ, when the Germ result might be one row beneath the blank. The Purity and Germ lookups are separate formulas in separate cells, but the point is that it is not skipping the blanks.

I guess my question is, is there any way to apply this type of formula to a VLOOKUP situation where the only criteria is that it skips blank results and finds the first real value?

For example: I am trying to look up cell B33 on the 'Test Result Table' and return the 2nd column of information, unless the cell it finds is blank. It needs to skip blank cells and return the first real value found when searching down the table.

How can I get this done?

Thank you for taking the time to help me. More than just time you have already saved me a lot of frustration. And I appreciate it.

**NBVC** · 02-17-2009, 02:08 PM

If I understand you correctly, you need just to add another condition to avoid blanks..

=INDEX('Test Result Table'!A1:L100,MATCH(1,('Test Result Table'!B1:B100=E33)*('Test Result Table'!C1:C100=C33)*('Test Result Table'!D1:D100<>""),0),12)

**tnfire** · 02-17-2009, 07:02 PM

It wasn't the format I needed, but the basic rule of how to apply a "Skip Blanks" criteria was what I was looking for. I was able to take that and apply it to the situation I was looking at, so thank you.