HLOOKUP Question

[swhaley]

I have started using the HLOOKUP function to simplify our proposal process at work and was wondering if there was any way to accomplish the last obstacle standing in my way.

The plans will each be under a unique identifier to search using HLOOKUP, however, some of the plans have "In-Network" and "Out-of-Network" under the same plan so all plans take up two columns, with some spanning both columns and others having the differentiation between "In-Network" and "Out-of-Network" rates. When I am using HLOOKUP, is there any way for it to pick up that second column if it is all contained under one unique identifier cell?

Thanks!
Seth

 

[sdsurzh]

can you post sample data?

 

[swhaley]

Here is a link to the Test Excel file I have been working with.

https://drive.google.com/file/d/0B0tL8Uy8loVdbldCSkZBWGZQcGM/edit?usp=sharing

 

[swhaley]

This file may make it easier instead of having to look through all of those plans... My apologies.

https://docs.google.com/spreadsheets/d/1a8TksRfZXR4QcD3zJBHt1akPxWzt6kSe4oZfSibWUT4/edit?usp=sharing

 

[sdsurzh]

Here is a link to the Test Excel file I have been working with.

https://drive.google.com/file/d/0B0tL8Uy8loVdbldCSkZBWGZQcGM/edit?usp=sharing

i have made it through macro. for that you have create a sheet name called "sheet1" and paste the below data from A1 (A1 will have plan name all unique names and B1 will network)
A1
Plan Name NetworkSS9
302
SU7
KV7
LF1
LFJ
KVX
KVY
SU9
KXR
TFZ
KXV
KX3
KX4
TH6
LF2
TH7
LF8
LGZ
LG1
KW4
KW5
TH3
LD4
TH4
LD9
LEP
LEO
LEU
LEV
LE1
LE2
SU8
KW7
TH5
LE8
LFE
LFF
LDW
LDX
TH8
TH9
MTL
TT6
LC8
TH2
GJX
HKM
GJY
HKN
PKN
LFH
KVT
KVU
HGK
KXN
HGM
KXT
HGQ
KXZ
LFW
LFX
PKQ
LF4
PKS
LGW
GJZ
KW1
LDY
LDZ
PIS
LD6
LEK
LEL
LER
LEQ
LEW
LEX
HGI
KW9
PKJ
LE4
PKL
LFB
PIQ
LDT
PKT
PKV
GJV
KT9
PHN
PIO
HG7
HGO
KVW
HGL
KXP
HGN
KXX
KX1
KX2
LFY
LFZ
PKR
LF6
LGX
LGY
HGH
KW3
LD1
LD2
PIT
LD8
LEM
LEN
LES
LET
LEY
LEZ
HGJ
KXL
PKK
LE6
PKM
LFD
PIR
LDV
PKU
KT7
GJW
KTT
PIN
PIP


After that press ALT+F11 and right click on the sheet and insert a module and paste the below code.

Sub Macro1()
Worksheets("sheet1").Select
Range("A2").Select
Range("b2:b500").ClearContents
Do
temp = ActiveCell.Value
tempadd = ActiveCell.Address
Worksheets("Plans").Select
Rows("7:7").Select
Selection.Find(What:=temp, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Selection.Offset(5, 0).Select
Range(Selection, Selection.Offset(1, 0)).Select

Selection.Copy
Worksheets("Sheet1").Select
Range("K1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K2:L2").Select
Application.CutCopyMode = False
Selection.Copy
Range(tempadd).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("K1:L2").ClearContents


Range(tempadd).Select
Selection.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""
Range("A2").Select
End Sub

Then press ALT+F8 and select macro1 and run

Regards,
SK

 

[swhaley]

Is there any way to do this without using a macro? The other Employees at my office are not very good with Excel and I want to make this as user-friendly as possible.

Thanks!