Vlookup --> find next

[Sunvisor]

Lets say you are using a vlookup to a value but there are multiple values with different results...

Is there a formula to do a vlookup and then in the row underneath say if vlookup = the above result, go to next?

 

[faraz502]

Dear, Aladin Akyurek

Sheet1

6588								down
6588								up
6588								down
2526								up
2526								up
2526								up
5452								down

<colgroup><col style="width:48pt" span="9" width="64"> </colgroup><tbody>
</tbody>

ok and

Sheet2

6588

2526

5452

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

I need all reasons of 6588,2526,5452 in a single cell

 

[Aladin Akyurek]

Dear, Aladin Akyurek

Sheet1

6588								down
6588								up
6588								down
2526								up
2526								up
2526								up
5452								down

<tbody>
</tbody>

ok and

Sheet2

6588

2526

5452

<tbody>
</tbody>

I need all reasons of 6588,2526,5452 in a single cell

1) If "down, up, down" in a single cell for 6588 is admissible, it's doable using a function a VBA along with native functions..

2) If you don't want repeats and having the unique reasons next to each other in the same row as 6588, a native formula aproach is possible.

Which do you want?

 

[faraz502]

i thing i need 2nd option.

thanks for your all replies

 

[Aladin Akyurek]

i thing i need 2nd option.

thanks for your all replies

Sheet1, F1:N8 (source)

Code								Reason
6588								down
6588								up
6588								down
2526								up
2526								up
2526								up
5452								down

<tbody>
</tbody>

Sheet2 (destination)

Code	Reason(s)
6588	down	up
2526	up
5452	down

<colgroup><col style="width:48pt" span="5" width="64"> </colgroup><tbody>
</tbody>

Define Ivec using Formulas | Name Manager as referring to:

=ROW(Sheet1!$N$2:$N$8)-ROW(Sheet1!$N$2)+1

B2, control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX(Sheet1!$N$2:$N$8,
  SMALL(IF(FREQUENCY(IF(Sheet1!$N$2:$N$8<>"",
  IF(Sheet1!$F$2:$F$8=$A2,MATCH(Sheet1!$N$2:$N$8,Sheet1!$N$2:$N$8,0))),
  Ivec),Ivec),COLUMNS($B$2:B2))),"")

See:

https://dl.dropboxusercontent.com/u/65698317/faraz502 version-2.xlsx

 

[markmzz]

Try this:

Layout

Code

Reasons

Sheet2

6588

down

up

down

2526

up

up

up

5452

down

******

**

**

**

**

**

**

**

********

*****

*****

*****

<tbody>
</tbody>

Formulas

In A2 - use only Enter to enter the formula

=IFERROR(INDEX(Sheet1!$A$2:$A$8,MATCH(0,INDEX(COUNTIF(A$1:A1,Sheet1!$A$2:$A$8),),0)),"")

In I2 - use Ctrl+Shift+Enter to enter the formula

=IF($A2="","",IFERROR(INDEX(Sheet1!$I$2:$I$8,SMALL(IF(Sheet1!$A$2:$A$8=$A2,ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1),COLUMNS($I2:I2))),""))

Markmzz

 

[faraz502]

it is possible i am connected excel with a networking software??

When network is down showing in Excel.

 

[Aladin Akyurek]

it is possible i am connected excel with a networking software??

When network is down showing in Excel.

What do you mean? Are you unable to open/use the file (supplied to you via the dropbox) in your network environment?

 

[faraz502]

i have a It room so i need when network is down in a department so excel collect the data related department issue and showing in a chart . it is possible??

 

[markmzz]

i have a It room so i need when network is down in a department so excel collect the data related department issue and showing in a chart . it is possible??

Hi Faraz502,

I'm sorry, but I don't know how to do that.

Maybe another user.

Markmzz

 

[faraz502]

Hi Faraz502,

I'm sorry, but I don't know how to do that.

Maybe another user.

Markmzz

Thanks Your reply I Hope some one give me solution. thanks again