Vlookup Next Record If Available

[gkn]

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

 

[James006]

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]

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]

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
	A	B	C	D	E	F	G	H	I
1	ID	NAME	IP				ID	NAME	IP
2	12	GAZAY	10.210.1		GAZAY|1		12	GAZAY	10.210.1
3	12	GAZAY	10.210.2		GAZAY|2		12	GAZAY	10.210.2
4	13	RAFOR	10.210.18		RAFOR|1		12	GAZAY	Not Available
5							12	GAZAY	Not Available
6							13	RAFOR	10.210.18
7							13	RAFOR	Not 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.

 

[James006]

@ Peter_SSs,

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

 

[gkn]

Hi Peter_SSs,

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

 

[Aladin Akyurek]

Just for the record:

Sheet1, A:C, the data. (Source)

ID	NAME	IP
12	GAZAY	10.210.1
12	GAZAY	10.210.2
13	RAFOR	10.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)

ID	NAME	IP
12	GAZAY	10.210.1
12	GAZAY	10.210.2
12	GAZAY	Not Available
12	GAZAY	Not Available
13	RAFOR	10.210.18
13	RAFOR	Not 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:

=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]

@Aladin,

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