Hi,

I would like to use Vlookup function in a macro to find out the value from a filtered data.

ex: Vlookup should check for the criteria in column C(from filtered data) and return the value from column D(from filtered data).

Please assist.

thanks

Hi,

VLOOKUP() cannot distinguish between visible and hidden rows, so you cannot use it for this.

The way to solve this really depends on the purpose of the filtering. Is the data being filtered to whittle down which value you want to look up (ie. filtering by criteria so only the match is visible)? Or for another reason?

Please clarify...

<OT>Dupe removed. Colin - you can click on "Report Post" (icon top right of each post) to notify a Mod if you come across Dupes, Spam, naughty posts etc.</OT>

pootle flump ur codings are working excelent.

Will do. Sorry, I keep overlooking the 'Report Post' option.

Colin,

pls refer to the attached file.

hope this explains.

thanks.

Hi,

I'm just going to post the text in your attachment on here so everyone can read it without having to download the attachment:

I've attached two different solutions to the problem.

Solution 1 uses an array formula to conditionally lookup the matching value:
The drawback on this approach is that if you have large tables, the calculation performance of the workbook will become impaired.

Solution 2 uses helper columns in column F on both sheets. The helper column contains the concatenated result of the two fields:
It then 'looks up' using the helper columns:
There is a potential issue with this approach, but I do not think it matters in this case.


Note that neither solution employs filtering and/or VLOOKUP().

colin,

many thanks! you have helped me a lot.

one question. how do i incorporate this in vb macro?

Hi,

Well say you go for option #2.

From VBA it's just a case of using the Range.Formula() property to put the formulas in the cells.

Hope that helps...

Excellent!!!

you are the man!!!

sorry to bother you again.

in the attached file the result is well executed.

but wen i do the same thing, i get only "10" in all the columns.

and i get #VALUE error when i apply the same rule in my own excel.

also in your attachement the parenthesis {} is seen in the formula bar and disappears when the cell is selected. this looks strange for me.

you have programmed a macro in the document. does it has any impact on the result?

Hi,
You'll have to show us the code and formulas you've tried. Please can you attach an Excel workbook?

I recommend that you go for option #2, not #option 1. But just for reference I will explain it -

The { } in option #1 indicate that the formula is an array formula.

If you were to manually enter the formula into the formulabar, you would type it in without the leading and final { } and press CTRL+SHIFT+ENTER to complete the entry. This will automatically insert the { } for you. The { } must not be entered manually. From VBA, to do this you would use the Range.FormulaArray() property instead of the Range.Formula() property.

Sorry, I don't understand this question - but perhaps it will be answered when we identify the problems with the code you have tried?

Hope that helps...

colin,

i am posting my macro. This is not matching the components properly. please have a look at it. consider my appollo hospitals example posted earlier.

***********************************************

'assume you have the below info in sheet("Volumes as per web")
C D
Laboratories 10
Service rooms 12
Operation Theatres 15
Laboratories 11
Service rooms 13
Operation Theatres 16

'assume you have the below info in sheet("Billing Information")
C D
Laboratories ?
Service rooms ?
Operation Theatres ?
Laboratories ?
Service rooms ?
Operation Theatres ?

'you need to fill in column D

Sub CollateVolumes()
Dim Client_BIC As String
Dim TotalClients, CLS_Client_Count, ClientID, NrOfBIMs, BIMend, BIM, BIMRowNr As Long
Dim rg1, rg2 As Long
Dim rng, rng2 As Range
TotalClients = Sheets("Control Panel").Range("L2").Value
Client_Count = 1

Do Until Client_Count = TotalClients
'assume Client_BIC = "Apollo Hospitals"
Client_BIC = Sheets("Control Panel").Range("B" & Client_Count).Value
'NrOfBIMs = 105
NrOfBIMs = Sheets("Control Panel").Range("E" & Client_Count).Value
'BIMend = 109
BIMend = Sheets("Control Panel").Range("F" & Client_Count).Value
'rg1 = 136
'rg2 = 142
rg1 = Sheets("Control Panel").Range("G" & Client_Count).Value
rg2 = Sheets("Control Panel").Range("H" & Client_Count).Value
Sheets("Volumes as per web").Select
Selection.AutoFilter Field:=1, Criteria1:=Client_BIC

'here my idea is to define the range as "C136142". Please refer to "set rng" in the next line; not sure if this is the right way to define the range. same for "set rng2" as well.
Set rng = Sheets("Volumes as per web").Range("C" & rg1, "D" & rg2)
Set rng2 = Sheets("Volumes as per web").Range("C" & rg1, "C" & rg2)
Sheets("Billing Information").Select
Selection.AutoFilter Field:=1, Criteria1:=Client_BIC
For BIMRowNr = NrOfBIMs To BIMend
'assume BIM = "Transaction Fee"
BIM = Worksheets("Billing Information").Cells(BIMRowNr, 2).Value
On Error Resume Next
If BIM = "Monthly Maintenance Fee" Then Worksheets("Billing Information").Cells(BIMRowNr, 5) = "1"
Else
'is this the right way to use Index and Match function in vba?
Worksheets("Billing Information").Cells(BIMRowNr, 5) = _
WorksheetFunction.Index(rng, WorksheetFunction.Match(BIM, rng2), 2)
On Error GoTo 0
Next
Client_Count = Client_Count + 1
Loop
End Sub

Hi,

I see that you are trying to derive the results by calling the WorksheetFunction object rather than by using formulas in the worksheet. Is using the worksheet to calculate the results unacceptable and, if so, why?

colin,

the no.of.components to be evaluated keeps changing everytime. using a formula to obtain the match volumes will not serve the purpose when the no.of.rows keeps increasing and decreasing.
also the information in sheet2 is not manually feeded. it is downloaded from the web and imported into excel.
i feel more convenient doing this with vba rather than using the formula.
pls assist.

Hi
Yes, but my suggestion is that you can use VBA to put the formulas into the worksheets. I mentioned before that you would use the Range.Formula() property to do it. This is more efficient than using the WorksheetFunction object to call the functions, not only because the WorksheetFunction is a fairly expensive call but also because the formula approach can be done without looping.