Vlookup --> find next

S

Sunvisor

Board Regular
Joined
Oct 9, 2009
Messages
233
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?
 
Sort by date Sort by votes
Dear, Aladin Akyurek

Sheet1

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

Sheet2
[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]
6588
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]2526
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]5452
[/TD]
[/TR]
</tbody>[/TABLE]

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

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
faraz502 said:
Dear, Aladin Akyurek

Sheet1

[TABLE="width: 576"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]6588[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]down[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]6588[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]up[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]6588[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]down[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]2526[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]up[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]2526[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]up[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]2526[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]up[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]5452[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]down[/TD]
[/TR]
</tbody>[/TABLE]
ok and

Sheet2
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]6588[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]2526[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]5452[/TD]
[/TR]
</tbody>[/TABLE]

I need all reasons of 6588,2526,5452 in a single cell
Click to expand...

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?
 
Upvote 0
faraz502 said:
i thing i need 2nd option.

thanks for your all replies
Click to expand...

Sheet1, F1:N8 (source)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD="width: 34"][/TD]
[TD="width: 38"][/TD]
[TD="width: 37"][/TD]
[TD="width: 34"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 64"]Reason[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]6588[/TD]
[TD="width: 34"][/TD]
[TD="width: 38"][/TD]
[TD="width: 37"][/TD]
[TD="width: 34"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 64"]down[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]6588[/TD]
[TD="width: 34"][/TD]
[TD="width: 38"][/TD]
[TD="width: 37"][/TD]
[TD="width: 34"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 64"]up[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]6588[/TD]
[TD="width: 34"][/TD]
[TD="width: 38"][/TD]
[TD="width: 37"][/TD]
[TD="width: 34"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 64"]down[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]2526[/TD]
[TD="width: 34"][/TD]
[TD="width: 38"][/TD]
[TD="width: 37"][/TD]
[TD="width: 34"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 64"]up[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]2526[/TD]
[TD="width: 34"][/TD]
[TD="width: 38"][/TD]
[TD="width: 37"][/TD]
[TD="width: 34"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 64"]up[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]2526[/TD]
[TD="width: 34"][/TD]
[TD="width: 38"][/TD]
[TD="width: 37"][/TD]
[TD="width: 34"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 64"]up[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]5452[/TD]
[TD="width: 34"][/TD]
[TD="width: 38"][/TD]
[TD="width: 37"][/TD]
[TD="width: 34"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 64"]down[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (destination)

[TABLE="class: grid, width: 400"]
<colgroup><col style="width:48pt" span="5" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Code
[/TD]
[TD="width: 256, colspan: 4"]Reason(s)[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]6588[/TD]
[TD]down[/TD]
[TD]up[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]2526[/TD]
[TD]up
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]5452[/TD]
[TD]down[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Define Ivec using Formulas | Name Manager as referring to:
Rich (BB code): 
=ROW(Sheet1!$N$2:$N$8)-ROW(Sheet1!$N$2)+1

B2, control+shift+enter, not just enter, and copy across:
Rich (BB code): 
=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
 
Upvote 0
Try this:

Layout

[TABLE="width: 275"]
<tbody>[TR]
[TD="width: 37, bgcolor: transparent"]Code[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 47, bgcolor: transparent"]Reasons[/TD]
[TD="width: 32, bgcolor: transparent"][/TD]
[TD="width: 33, bgcolor: transparent"][/TD]
[TD="width: 32, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Sheet2[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]6588[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"]down[/TD]
[TD="bgcolor: yellow"]up[/TD]
[TD="bgcolor: yellow"]down[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]2526[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"]up[/TD]
[TD="bgcolor: yellow"]up[/TD]
[TD="bgcolor: yellow"]up[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]5452[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"]down[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]******[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Formulas

Code: 
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
 
Upvote 0
it is possible i am connected excel with a networking software??

When network is down showing in Excel.
 
Upvote 0
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??
 
Upvote 0
faraz502 said:
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??
Click to expand...
Hi Faraz502,

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

Maybe another user.

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Vertical VLOOKUP
Replies
5
Views
48
Peter_SSs
Peter_SSs
S
  • Solved
VLOOKUP with IF or LOOKUP - Which is the right one to use?
Replies
5
Views
308
6StringJazzer
6StringJazzer
megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
286
etaf
etaf
R
  • Question Question
VBA- Adding source and destination cells together rather than overwriting
Replies
4
Views
250
Alex Blakenburg
A
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
162
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,335
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top