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?

 

[Aladin Akyurek]

Anyway to make this arrayformula show found results in the column beside, instead of new rows?

Which array formula? And do you mean returning results columnwise instead of rowwise?

 

[mrfahaji]

Hello,

I've just found this thread and the explanation provided is demonstrating almost exactly what I want to do. However, after transposing the cell references, I was getting a #NAME? error. So, I copied your table into excel and copied your array formula into the F column, and I get a #NAME? error as well.

Any idea why this might be happening? Either your formula doesn't work, even with the data you've provided, or I'm doing something wrong. It's probably the latter, so any help would be appreciated!

 

[mrfahaji]

Ignore my previous post, I was being an idiot (copying and pasting the formula doesn't work because it says 'Value' instead of B:B, for example, ha).

It also works with names instead of values, which is what I needed - thanks!

 

[faraz502]

My Sheet1 Format is



=========================================
Sheet1

[TABLE="class: cms_table, width: 576"]
<tbody>[TR]
[TD]Cell F2
Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cell N2
Reasons[/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]down[/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]up[/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]down[/TD]
[/TR]
[TR]
[TD]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]up[/TD]
[/TR]
[TR]
[TD]2526
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]up[/TD]
[/TR]
[TR]
[TD]2526
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]up[/TD]
[/TR]
[TR]
[TD]5452[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]down
[/TD]
[/TR]
</tbody>[/TABLE]

=========================================

and i need
Reasons in Sheet2 N2 Against F2 Codes

[TABLE="class: cms_table, width: 576"]
<tbody>[TR]
[TD]Cell F2
Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cell N2
Reasons[/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5452[/TD]
[/TR]
</tbody>[/TABLE]

thanks,

 

[faraz502]

My Sheet1 Format is



=========================================
Sheet1

[TABLE="class: cms_table_cms_table, width: 576"]
<tbody>[TR]
[TD]Cell F2
Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cell N2
Reasons[/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]down[/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]up[/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]down[/TD]
[/TR]
[TR]
[TD]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]up[/TD]
[/TR]
[TR]
[TD]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]up[/TD]
[/TR]
[TR]
[TD]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]up[/TD]
[/TR]
[TR]
[TD]5452[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]down[/TD]
[/TR]
</tbody>[/TABLE]


=========================================

and i need
Reasons in Sheet2 N2 Against F2 Codes

[TABLE="class: cms_table_cms_table, width: 576"]
<tbody>[TR]
[TD]Cell F2
Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cell N2
Reasons[/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5452[/TD]
[/TR]
</tbody>[/TABLE]


thanks,

 

[Aladin Akyurek]

@faraz502

Sheet2, N2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$N$2:$N$8,SMALL(IF(Sheet1!$F$2:$F$8=$F2,
  ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),COUNTIF($F$2:F2,F2))),"")

 

[faraz502]

Thankx

Aladin Akyurek

I am trying your formula but i found B/M Result. i need all matches in a cell EXP: if 6588 have three reasons so all reasons are mention in N2. If you are given solution its very helpful for me.

Thanks

[TABLE="width: 576"]
<colgroup><col style="width:48pt" span="9" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]F2
Code
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64"]N2
Reasons
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]6588
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]down
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]up
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]6588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]down[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]up[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]up[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]2526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]up[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]5452[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]down[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Thankx

Aladin Akyurek

I am trying your formula but i found B/M Result. i need all matches in a cell EXP: if 6588 have three reasons so all reasons are mention in N2. If you are given solution its very helpful for me.
...

I don't know what a B/M result is. By the way, control+shift+enter means: Press down the control and the shift keys while you hit the enter key.

See:

https://dl.dropboxusercontent.com/u/65698317/faraz502.xlsx

 

[faraz502]

I don't know what a B/M result is. By the way, control+shift+enter means: Press down the control and the shift keys while you hit the enter key.

See:

https://dl.dropboxusercontent.com/u/65698317/faraz502.xlsx

thanks,

sorry but not helpful for me i need all reasons in a single cell against first code

 

[Aladin Akyurek]

thanks,

sorry but not helpful for me i need all reasons in a single cell against first code

Your output exhibit does tell otherwise. What do you want to see as outcome for 6588?