Excel search

[hakki13]

Hi, I have a had this problem multiple times. I have columns A, B, C and in A I have running number from 1,2,3... in B, C I have names. I would like to create a search, where it would search columns B, C for the names I have chosen. When it finds those names on a same row, it would return me biggest number on the running column A.

So, trying to find names Joe and Krista on columns B, C (does not matter if Joe is in C and Krista is in B, or other way around) it would return me that rows running number from A. There is multiple times when Joe and Krista meet so that is why largest/latest meeting. Hopefully you understand. Thanks in advance.

[bebo021999]

Assuming that running numbers from A1:A8
Names are in B1:C8
D1 and E1 contains 2 names
Try this:
=MAX(LOOKUP(2,1/(B1:B8&C1:C8=D1&E1),A1:A8),LOOKUP(2,1/(B1:B8&C1:C8=E1&D1),A1:A8))
Find "JoeKrista" and "KristaJoe" in combination of the two name columns, then using MAX to get the last position from both results
Does it meet?

[hakki13]

Nicely done, really happy man here

[hakki13]

one more thing, if I wanted to do it with a LARGE function just get earlier meeting also, how would I go and change it?

[bebo021999]

This is interesting question and I like to deal with it!
We are comparing the last 2 meeting with MAX to get the larger, now we use MIN to find the smaller (= the earlier meeting also)

[hakki13]

if there are 50 meetings? I thought LARGE would be a way to go.

[bebo021999]

Sorry pls ignore #5 I've just found the error.
The correct should be:
=LARGE(IF((B1:B8&C1:C8=D1&E1)+(B1:B8&C1:C8=E1&D1)=1,ROW(B1:B8),""),2)
Mark 1 for the meeting position, then mark the row number, then use LARGE with 2 to get the earlier meeting

[hakki13]

Thanks, will try it right away.

[bebo021999]

if there are 50 meetings? I thought LARGE would be a way to go.

50 or 100, no matter, because I am finding last position of Name1-Name2 and Name2-Name1. I found error to use the MIN because if there are 2 Name1-Name2 as latest meeting, MIN still returns the last pos of Name2 - Name1

[hakki13]

Ok, I get your point, but this is not getting the largest value in column A, right? this is giving me largest row number?? Sry, for being pain in the ****.

[bebo021999]

I understand now.
Try this

=LARGE(IF((B1:B8&C1:C8=D1&E1)+(B1:B8&C1:C8=E1&D1)=1,A1:A8,""),2)

Confirmed with holding both ctrl-shift, then enter

Returns value in A1:A8 instead of row number

[hakki13]

Great, this works perfectly.

[hakki13]

Hi,

Small continuation to this problem: LARGE(IF(OR($C:$C=$D3;$J:$J;$D3);$A:$A;"");C16)

Can't get the formula above to work, can anyone see my mistake? Trying to return largest value from A:A, if cell D3 matches C:C or J:J.
A:A is column 1,2,3,4,5,6
C:C is A,B,D,B,D,A
J:J is B,D,A,D,A,B

So if I have B in cell D3 I would like formula return 6,4,2,1 (these are ofcourse in different cells,