Indexing/Lookup help

[hokiedrum]

Hi all,

I am having some issues indexing some data into an order that I can use for further calculations. I've attached a sheet showing how the data needs to be shown/what I'm currently doing. I am pulling data from an instrument in the format on the 'Data' tab; two columns (C and D, E and F, etc), one with elements and one with concentrations. As seen, I have multiple points per element per date. I only need the point closest to the desired concentration entered in column C of 'Data Pull'. There is currently dummy data along with dummy dates that I made up to check the sheet. There are currently two problems though, the first of which is the bigger concern.

1) No matter what I do, the Li set will not pull. Despite the formulas in column H of 'Data Set' being all the same for each element, I am getting #VALUE! errors. I've asked several colleagues and none of us can figure out the issue. Can anyone else see where it's stemming from?

2) The sheet takes forever to calculate; I realize that is likely due to the fact that the indexing formulas are rather complicated, but is there anything that I might do to cut down on this time taken?

I appreciate any help you all can offer!

Zach

Conc pull help.xlsx

[twiggywales]

its a bit confusing what your trying to do,

I can see why its so slow, you have ranges referencing whole columns that will make things slooooow (especially with the amount you have)

as fir the looking up have you thought about trying a different kind of solution (index match)

as I don't really understand what your are trying to do I will struggle to help further

[hokiedrum]

UPDATE: Actually, switching the lookups to look from row 3 to 500 rather than the entire column fixed all of my issues. Not sure why it wasn't working with just one of the elements, but hey, if it works I won't complain. Thanks for your help!


Sorry, I know it's a little confusing; I guess where I've been looking at it a lot I didn't explain quite well enough. I'll try to break down further.

Basically, I have two columns of data per data set (each has a date entered at the top of 'Data' in row 1). The first column (C on 'Data') has an elemental symbol in the left two characters. In the second (D on 'Data') I have a number for that element. I enter the target concentration for each element on the 'Data Pull' sheet. The dates are pulled into column F on 'Data Pull', so for each data set I want the value closest to the target value per date.

I attached a new sheet to show a little better. Basically, I want cell J1 to tell the sheet "look at I1, then look at the cells in column B that match up with cells in A that are equal to I1, then pull the closest value from those cells to what is in B1". I can have varying numbers of points for each element in each data set, so I can't say "look from B5 to B8 and pull the closest to B1".

In this case, J1 would be equal to the closest value to 20 (21 in the sheet) in cells B5:B8. J6 would equal 20, the closest to 20 in E5:E9. M3 would be 14 (looking at B9:B11), and M6 would be 15 (E10:E11).

Hopefully that explains a little better. I will change the formulas to not reference the entire columns though; I didn't realize how much that slowed it down. Thanks!

Edit: I have looked at other ways of looking the values up, but I didn't find much. I don't fully understand index match, but I'll look at it a bit more.

Conc pull new.xlsx