Numeric value from a text string/numeric identifier?

Hi,

I am trying to return a numeric value if it is associated with a particular text string. The text strings and numeric values are in the same column (H), with the numeric value being in the row directly above the text string. Each of the values (numeric and text) can be identified by unique references (numbers) that are in adjacent columns (K and L).

Thus, for example, on the sheet named 'CSV 19', we have (apologies for columns not lining up):
H K L
2.13 936 36
Inert 936 348
1.12 936 36
Non-Haz 936 348

I am trying to find a formula that says: "If K=936 and L=348 and H = 'Inert', then return value in H where K=936 and L=36" (i.e. 2.13). I also want the formula to sum all the numeric values in column H where inert occurs.

Is this possible and if so, can anyone help?

Many thanks,
Lamp

I have inserted a formula into column G, to identify the text rows, by taking the row number and adding 0.1 to the row number.

I have then added formulas in columns M to O, to transfer the row below to the same line as the numbers.

In cloumn P I have then applied the formula to look for 936, 348 and Inert and put the word inert in as a result.

The sum of inerts is a simple Sumif formula in O1.

I have attached a sample in the file Sample.xls

Regards

Gary

Fantastic! Many thanks Gary and sorry for not replying sooner (threads sure move fast on this forum!). I have been able to take your solution, expand it a little and apply it to my problem with success. That's not to say I won't need help again in the future....watch this space!

Lamp