Vlookup Next Record If Available

gkn

New Member
Joined
Mar 27, 2013
Messages
6
I am looking a formula to retrieve next record of vlookup. Let me clear.

my Source Sheet is this; (not sorted by name; there may be same names in cells after some other names)

ID NAME IP
12 GAZAY 10.210.1
12 GAZAY 10.210.2
13 RAFOR 10.210.18


My destination will be like the following;

ID NAME IP
12 GAZAY
12 GAZAY
12 GAZAY
12 GAZAY
13 RAFOR
13 RAFOR



If i am clear till here, what i need to do is this: I will vlookup the ip adressess it should bring me the all records every time, but in my destination sheet i have four (4) same name "GAZAY" however in source sheet i have two "GAZAY" name. So if available i need to vlookup and bring the allrecords when it is not available it should give me #NA or something not available string.

I hope it is understandable, sorry my poor language


briefly:
my source page is this: http://imgim.com/7775incix1891334.png
What i need is this: http://imgim.com/1793incia8944647.png (it should find all ip adressess if available)


Thanks in advance, and i appreciate
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

In order to return more than one value from a "vlookup" approach, there are only two possibilities :

1. An array function, or
2. A macro

Which solution would you favor ?
 

gkn

New Member
Joined
Mar 27, 2013
Messages
6
Hi James,

I am not too good with excel macros, but i would prefer macro.
Cause i have a programming background, i may understand the algorithm easily.

Thanks.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,526
Office Version
  1. 365
Platform
  1. Windows
In order to return more than one value from a "vlookup" approach, there are only two possibilities :

1. An array function, or
2. A macro
There is at least one other option, and that is to use a helper column and standard, non-array, formulas..

I've done all this in one sheet but is adaptable to two if it looks like what you want.
The helper column (E) can be hidden after it has been populated with the formulas.

Both formulas copied down

Excel Workbook
ABCDEFGHI
1IDNAMEIPIDNAMEIP
212GAZAY10.210.1GAZAY|112GAZAY10.210.1
312GAZAY10.210.2GAZAY|212GAZAY10.210.2
413RAFOR10.210.18RAFOR|112GAZAYNot Available
512GAZAYNot Available
613RAFOR10.210.18
713RAFORNot Available
8
Lookup Values



gkn: Welcome to the MrExcel board!
I suggest that you investigate my signature for posting screen shots like I have as most helpers won't bother if they have to manually type out sample data to test. Using one of the methods I've suggested allows users to copy the data and paste into their own sheets.
 
Last edited:

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680

ADVERTISEMENT

@ Peter_SSs,

Agree with you ... the shortest path to the solution is a helper column to get a unique identifier ...
 

gkn

New Member
Joined
Mar 27, 2013
Messages
6
Hi Peter_SSs,

This is certainly what i want, that's awesome, thanks.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Just for the record:

Sheet1, A:C, the data. (Source)
IDNAMEIP
12GAZAY10.210.1
12GAZAY10.210.2
13RAFOR10.210.18

<colgroup><col style="width: 48pt;" span="2" width="64"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3242;" width="91"> <tbody>
</tbody>

Sheet2, A:C, the processing. (Destination)
IDNAMEIP
12GAZAY10.210.1
12GAZAY10.210.2
12GAZAYNot Available
12GAZAYNot Available
13RAFOR10.210.18
13RAFORNot Available

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2503;" width="70"> <col style="width: 77pt; mso-width-source: userset; mso-width-alt: 3640;" width="102"> <tbody>
</tbody>

C2, Sheet2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(INDEX(Sheet1!$C$2:$C$4,
  SMALL(IF(Sheet1!$A$2:$A$4=$A2,
  IF(Sheet1!$B$2:$B$4=$B2,ROW(Sheet1!$C$2:$C$4)-ROW(Sheet1!$C$2)+1)),
  COUNTIFS($A$2:A2,A2,$B$2:B2,B2))),"Not Available")
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
@Aladin,

Absolutely Brilliant Solution ... with no helper column and no VBA ...!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

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
Top