Match and return functions

[LiAD]

I am trying to get a function to look through a list of 200 parts to match
two text strings, then return a value that corresponded to the last entry
made. Example

columns
D E F
Apples 32 35
Pears 21 20
Beans 55 99
Apples 35 19 in cell e5 i would like a function to return 35
(the f col entry
of the last apples entry) and the user then
enters whatever
they need in B say 19
Oranges 21 25
Apples 19 34 the function should return 19 from cell B4

There may be random number of spaces between the xth apple entry and the x+1
entry hence why i need a searching type function. I only need the function
to work for apples, no other text entries need searched from the list.

Does anyone know how I could get this to fly?

Thanks
LD

 

[Rick Rothstein]

You can use this worksheet Change event code to do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
If UCase(Target.Value) = "APPLES" Then
For X = Target.Row - 1 To 1 Step -1
If UCase(Cells(X, 1).Value) = "APPLES" Then
Target.Offset(0, 1).Value = Cells(X, 3).Value
Exit For
End If
Next
End If
End If
End Sub

To implement this code, right-click the tab at the bottom of the worksheet
an select View Code from the popup menu that appears; then copy/paste the
above code into the code window that appeared. Now, go back to your
worksheet. Whenever you type "Apples" (in any letter casing) into Column A,
the last "Apples" entry from Column C will be copied into the Column B cell
next to it

 

[Gary''s Student]

It is very clear that you would be in great shape if the order in the columns
were reversed. That way you could use CONCATENATE() and VLOOKUP() to find
the first occurance. So invert the columns. See:

http://www.cpearson.com/excel/lists.htm#ReverseOrder

 

[LiAD]

Unfortunately inverting columns can't be done in this case - otherwise i
would love too.

Thanks for the tip anyway, never seen concatenate before

 

[Duke Carey]

The formula(s) I gave you in response to your earlier post work just fine -
so long as you enter them as array formulas, by pressing Ctrl-Shift-Enter.
Use this version and ignore where the data starts. Just adjust the end of
the range to reflect where your data ends

=index(B1:B100,max(row(a1:a100)*(A1:a100="Apple")))

 

[LiAD]

Don't know what I am doing at all but the message i get is =index(f22:f26,26)
and it flops. the other two bits of the formula evaluate fine but the index
isn't working for some reason.

 

[Rick Rothstein]

It did not seem like a formula solution was a practical way to go to me
because the table of data appears to be dynamic and the location of the next
Apples entry cannot be known in advance. I guess you could put a formula in
every cell of column B and then let the user overwrite it with an actual
number if something other then Apples was entered into Column A, but then
correcting a mistaken entry could be a problem later on. My choice was for
the worksheet event code that I posted earlier.

 

[Duke Carey]

Well, you're trying to find the 26th item in a 5 item array - that just ain't
gonna work.

The size of the 3 ranges has to match, so go with F1:F26 & try it again

 

[Rick Rothstein]

Just out of curiosity, are you able to see my first posting in this thread?
I ask because you have not responded to it. Is it that you can't or don't
want to use a VBA solution? I think a formula solution is problematic
because you appear to dynamically be adding data to the list with the
frequency of "Apples" entries unknown. Not know where the next "Apples"
entry is means (as far as I can imagine) you will need a formula in every
cell of Column B and, if the entry is not "Apples", you would have to
overwrite the formula with a number. Correcting any mistaken entries would
seem to become an issue then.

 

[LiAD]

the three ranges are exactly the same size in the function i am using. I had
started the range with $f$22:f26 as i want the function to look from the
first cell which is in row 22 down the list as i copy the formula down so
when i get to cell 200 it will look from f22 to f100 and give the last one it
finds. If I use a formula in every one of the 200 cells with the same range
i'll get the last value in table in every cell with apples. if i want it to
look at preceeding entries then i do not need the formula to go past whatever
row i am currently in.

i had assumed from your description that your formula would only give me the
last value for apples in the table in every cell i put the formula. is this
not true?

 

[LiAD]

yeah sorry,

obviosuly i am a bit of a ameteur and coding is well out of my league so i
thought i would try the formula approach. i have just tried ur code but
couldn't actually get it to work for the cells i wanted.

my text list starts in col D row 26 now (been changed) with the data I want
it to search for in col F. the result i want in col E beginning row 27. the
text list i am searching has 4 entries from a drop down list, and also the
result is text from a drop down list, not numbers - not sure if this changes
your approach.

sorry i thought i could adapt the cols and rows to suit from whatever i was
given but apparently i'm snookered.

Thanks
LD

 

[Duke Carey]

This works perfectly for me:

=INDEX(F22:F26,-21+MAX(ROW(E22:E26)*--(E22:E26="Apple")))

though it will return an error if Apple is not found.

 

[Rick Rothstein]

Try this modified code...

Private Sub Worksheet_Change(ByVal Target As Range)
Const DataRow As Long = 26
Const DataColumn As String = "D"
Const CopyToColumn As String = "E"
Const CopyFromColumn As String = "F"
Dim X As Long
If Target.Count > 1 Or Target.Row < DataRow Then Exit Sub
If Target.Column = Columns(DataColumn).Column Then
If UCase(Target.Value) = "APPLES" Then
For X = Target.Row - 1 To DataRow Step -1
If UCase(Cells(X, DataColumn).Value) = "APPLES" Then
Cells(Target.Row, CopyToColumn).Value = Cells(X,
CopyFromColumn).Value
Exit For
End If
Next
End If
End If
End Sub

Same install procedure (right click tab, select View Code, copy/paste above
into code window that appears). Note that I added some Const statements
(which are user defined constants for use in the procedure) where you can
set the data start row and 3 columns you are interested in that you can set
yourself (in case you "move" them around again in the future).

 

[Rick Rothstein]

Cells(Target.Row, CopyToColumn).Value = Cells(X,

CopyFromColumn).Value

The above (located just before the Exit For statemen) is supposed to be a
single code line... the newsreader broke it up into two lines. Here is the
code line as it should look in the code that you copy/pasted...

Cells(Target.Row, CopyToColumn).Value = Cells(X, CopyFromColumn).Value

 

[LiAD]

tried this one works well thanks.

also got given a lookup version which works well too - i can understand it
easier rather than the program approach so i think i'll go with it.

thanks for your help. something else learned in any case!

cheers