Find the first instance of a value, and return the value of the cell next to it.

I need a function that returns the value of a cell, triggered by a positive identification of a value in the cell one column left of the target cell.

In the spreadsheet attached, I get the list of information from an instrument that auto generates a excel report.

I take the info and copy it and paste it into this spreadsheet.

I want the cells under the red "Inj Volume" to auto find the Calibration levels "1, 2, 3, 4" and return the value of the cells right next to those levels.

So, the first cell under the red "Inj Volume" would return "5.00" and the second cell should return "10.00." The catch is, sometimes the list changes row position (column position remains constant).

The 2 tabs are 2 different lists, as an example.

Thanks John, that works.

I almost expected a Vlookup answer, but I don't understand enough about them to craft it very well. When I read the excel help files, it's too technical for me to understand well, but I think I might be getting a little better handle of it now.

So, as I understand it, the 1st position is "1" and looks for the value 1. (or 2 or whatever I'm looking for)
The 2nd position means it looks at the leftmost column of the 2 columns selected
The 3rd position is to return the value from column "2" of the 2 columns selected
The 4th position... not sure what that does...

Am I understanding it right, and what does that 4th item do?

Thanks garfield. Your solution works as well, but I do not understand how it works at all. What is the equation doing? Can you explain it for me?

All I know is if I change the 1 to 2, it changes which one it is looking at.

According to the excel help file, the "sumproduct" function multiplies things, and then adds them, and non-value amounts equal 0. This is being used very differently by you. I'd like to understand it. Can you explain it?

Originally Posted by timmtamm

According to the excel help file, the "sumproduct" function multiplies things, and then adds them, and non-value amounts equal 0. This is being used very differently by you. I'd like to understand it. Can you explain it?

SUMPRODUCT is a very powerful function.

See this for lots of useful info:

http://xldynamic.com/source/xld.SUMPRODUCT.html