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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
88
Peter_SSs
Peter_SSs
R
  • Question Question
IF statement followed by VLOOKUP followed by another IF statement
Replies
2
Views
54
kvsrinivasamurthy
K
S
  • Solved
VLOOKUP with IF or LOOKUP - Which is the right one to use?
Replies
5
Views
486
6StringJazzer
6StringJazzer
C
  • Question Question
Building a VLookup or Lookup or Pivot table
Replies
0
Views
43
cogswel__cogs
C
megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
390
etaf
etaf

Forum statistics

Threads
1,224,894
Messages
6,181,618
Members
453,057
Latest member
LE102024

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