MS EXCEL experts out there, I need help!!!!!

For example, the following is the original table:

From/To indicates the range for the "value"

Item From To Value
200000002 0.000 0.189 3423
200000002 0.189 0.830 3424
200000002 0.830 3.943 3433
200000002 3.943 4.393 865
200000002 4.393 5.607 3454
200000008 0.000 1.278 8889
200000008 1.278 5.432 5587
200000010 21.625 21.812 4564
200000010 21.812 22.468 4777
200000010 22.468 24.624 4556
200000012 27.068 41.089 6689
200000012 41.089 42.007 7688

I want correct values in the "Value" column in the following table:

Item Location Value
200000002 0.261 (the right value is 3424)
200000008 0.5 ???
200000008 3.698 ???
200000008 4.32 ???
200000008 15.396 ???
200000008 15.757 ???
200000008 16.417 ???

How do I get the right values in the "Value" column. I used VLOOKUP function of Excel but the problem is that VLOOKUP only looks at the first match in the "Item" field. How do I make it to look at the next row when the first match does not meet the criteria? I tried VBA/Macro but my knowledge is limited in these fields.

Helps will be appreciated.

the tables got messed up!!!!!!!!!

I TRIED TO MAKE THE TABLES CLEAR !!!!

The original table:

From/To indicates the range for the "value"

Item____________From_______To_______Value
200000002_______0.000_______0.189_______3423
200000002_______0.189_______0.830_______3424
200000002_______0.830_______3.943_______3433
200000002_______3.943_______4.393_______865
200000002_______4.393_______5.607_______3454
200000008_______0.000_______1.278_______8889
200000008_______1.278_______5.432_______5587
200000010_______21.625_______21.812_______4564
200000010_______21.812_______22.468_______4777
200000010_______22.468_______24.624_______4556
200000012_______27.068_______41.089_______6689
200000012_______41.089_______42.007_______7688

I want correct values in the "Value" column in the following table:

Item_____________Location_______Value
200000002_______0.261_______(the right value is 3424)
200000008_______0.5__________???
200000008_______3.698________???
200000008_______4.32_________???
200000008_______15.396_______???
200000008_______15.757_______???
200000008_______16.417_______???

Your question does not make any sense.

Just imaging you are someone else reading your question. Can you figure out whatever the heck the writer is talking about?

Hi,
I might be able to help you out with this. Couple of questions: In your 'location' column, the numbers you have generated is not the same as the original column.
200000002_______0.189_______0.830_______3424
200000002_______0.261_______(the right value is 3424)

How did you get the right value? Is there a special formula involved or you randomly generated the numbers?

Since it is excel, the numbers you have written there are the rows and columns dependent.

If you can give a screenshot of the spreadsheet you are working on, it would give more sense and hopefully closer to helping you.

How come the end values, I mean the column 'To' has value which coincides with the value in the 'From' column in the following rows?
for example you see .189 in the first row as the 'To' value and
shows up again as .189 in the second row in the 'From' value..

This problem has been given a twist in the presentation part

A quick question if you are still around: is 'location' your range of 'to' and 'from'?

Hmmm... is this Shanka fella gonna let us pick our brain and vanish??? From his posting, it looked like he needed urgent help...now we are all ready to go, but he is gone!! What happened to respecting other people's time??

hey, bhusan:

I guess, I made my question a lil complicated. actually, i want the correct value in the second table. e.g., the value in the first row in the second table should be 3424 (by observation) as location 0.261 is in the range of 0.189 to 0.830 in the first column, right? similarly, the value in the last row at location 16.417 against Item 200000008 is none as this location is beyond the range of 0 to 1.278 and 1.278 to 5.432 against Item 200000008 in the first table. i have thousands of data. so the question is how do i make excel to do it for me, ie, return me the right value at a location of an item as specified by the range.

hey, bostongirl:

Yes the location is not the same. these are the given locations at which i need the correct values from first table in which the values are specified by the range. clear??

hey, Slackdemic:

I will try to include the screenshots!

hey, TM:

Yes. thats true. but thats how i got the data - thousands of entry. i guess we will have to ignore that part.

GUYS!!! LEMME KNOW IF U STILL VE QUESTIONS.

value in the first row in the second table should be 3424 (by observation) as location 0.261 is in the range of 0.189 to 0.830 in the first column, right?


No, how can 0.261 fall in the range of 0.189 to 0.830?

the screenshots!!

i kind of think without VBA/Macro the problem is not going to solve. tell me if i m wrong.

I doubt Vlookup is going to be of any help in this. I tried couple of combinations and it doesnt give you the right 'value' because its not capable of recognizing where the 'location' falls in between the ranges. You might want to go through macros in this one.

hey, bostongirl!

exactly!

but i never worked with macros before. was trying to write a macro but it did not work.

hey, bostongirl! can you tell me whats wrong with following. it did not work.

Sub try()
'
' route1 is the table_array.

Do

If IsEmpty(ActiveCell) Then

If ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>=VLOOKUP(RC[-2],route1,2,0),RC[-1]<=VLOOKUP(RC[-2],route1,3,0)),VLOOKUP(RC[-2],route1,5,0),""Error!!!"")" = "Error!!!" Then

route1.Range (x + 1)

Else

ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>=VLOOKUP(RC[-2],route1,2,0),RC[-1]<=VLOOKUP(RC[-2],route1,3,0)),VLOOKUP(RC[-2],route1,5,0),""Error!!!"")"

End If

End If

ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell = "g"

End Sub

here is your macro bro!

Sub test()
For i = 2 To 13 ' number of data rows in input array
For j = 2 To 8 ' number of data rows in output array
If Cells(j, 7) = Cells(i, 1) And Cells(j, 8) >= Cells(i, 2) And Cells(j, 8) <= Cells(i, 3) Then
Cells(j, 9) = Cells(i, 4)
End If
Next j
Next i
End Sub

NOTE:

you have to play with i and j values. this module works for the number you have put up in your first post. i'm attaching my excel snapshot. i guess you can go from there. if not email me and i can help. you have to change i, j and column values in IF statement as per the location of your tables. my best advice is try to match your spreadsheet as how i did. :) hope this helps.

here is the vba module

hyaa... Good work!
:)

hyaaaaaaaaaaaaa!!!!

gr8! thanks bro.