Pulling the second correct result from Vlookup.

Is there a way to use either Vlookup or maybe SUMPRODUCT to pull the second or third qualifying result instead of the first result?

If so, how.

If not, then what can I use to automate a search of data that has more than one instance of qualifying information.

For an example, see the spreadsheet.

Try this array formula:

=IFERROR(INDEX($I$4:$I$15,SMALL(IF($B$27=$A$4:$A$15,ROW($A$4:$A$15)-ROW($A$4)+1),ROWS($1:1))),"")

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

the way you have imported this data from you Mass Spectrometer has made this a bit more difficult for you to manage as there are merged cells everywhere. Is it an HP or Waters? Masslynx is much easier to work with!!

Try this one

Formula:

Please Login or Register  to view this content.

You should really get rid of all merged cells. They are nothing but trouble.

Glenn
It is an Agilent LCMS.

The formula you gave returns the first result. How do I manipulate it to return the 2nd, 3rd, and so on of the same sample name?

Mmmm. You must be doing something wrong...

Agilent - sorry, yes that's the one. Their software is horrible. You'd be better to de-merge everything you D/L from the instrument BEFORE you do any data manipulation. Are you looking to do ion ratios, etc?

In the meantime. here's the sheet, with the array formula in place....

Alkey,

Your formula gives me the 2nd result, but if I kill all the merged cells and extend the range to the rest of the table, I get the 4th result. If I change your "2" to a "1" I get the 3rd result. What do I need to do to get each of the results, 1st through 4th?

Glenn,
Ok, that's better. Now I understand where I need to change the numbers to manipulate the formula. Before, I was trying to change the "+1" to a "+2", and that didn't work. Then I tried changing BOTH of the row numbers at the end, and that didn't work either. Now I see, I only change the last number.

No, not looking at Ion ratios. I have to use the area to calculate the concentration, then the second area to create a second, separate concentration. Then I have to calculate a CV value between the 2. That is to determine how well the injector is performing.

Thanks.

If you unmerge cells you can utilize the entire column and get the second value

as regular formula

Formula:

Please Login or Register  to view this content.

Alkey,
That works nicely. Thank you.
I see that the "2" is the result row to pull from.

Out of curiosity, what are the 15 and 6? What do they do?

edit: never mind. I seem to have found the explanation for the 15 and 6 in excel.

Originally Posted by timmtamm

Alkey,
That works nicely. Thank you.
I see that the "2" is the result row to pull from.

Out of curiosity, what are the 15 and 6? What do they do?

Aggregate is a multi-purpose function that includes 19 different very useful functions. 15 is SMALL and 6 is to ignore errors. This combination allows to enter an array formula without use of CTRL+SHIFT+ENTER key combination. Aggregate was first introduced in Excel 2010.

Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).