Lookup syntax

[Kevinski]

Hi

I have created 3 named lists of values: 'RESI' 'COMM' and 'OTHER'

I want to know the syntax for looking a value returned on a master sheet up in each of these 3 lists and returning the name of the list it is found in.

I am familiar with VLOOKUP and LOOKUP but not sure how to nest IFs nor how to ask if a value is "in" a list.

So what I'm aiming for is something like "Search for the value in cell A1 in RESI, COMM and OTHER and when you find it return me the name of the list you found it in to cell B1"

While I'm at it I may as well ask you good folk for the code which enables me to work through the master list row by row performing the above until the fist blank cell/ row is found and then quit.

Many thanks

 

[Gavona]

Could the value be in more than one of the lists? If not then why not combine the three lists into one longer one with a new column to indicate which list it is. Then a simple vlookup would do it.

not sure how to nest IFs nor how to ask if a value is "in" a list

. If vlookup doesn't find a match it returns an error value - check for this.
Start by checking the first list and making that formula work:
=if([red]isna(vlookup(mycell,list1,1,0))[/red],[blue]"check list 2"[/blue],"In list1")

then develop this:
=if([red]isna(vlookup(mycell,list1,1,0))[/red],[blue]if(isna(vlookup(mycell,list1,1,0)),[blue]"check list 3"[/blue],"In list2")"[/blue],"In list1")


(I haven't checked the code but it should give you the idea)



Gavin

 

[KenWright]

Another Option:-

=CHOOSE(1+COUNTIF(RESI,C28)*1+COUNTIF(COMM,C28)*2+COUNTIF(OTHER,C28)*3,{"Not Found","List1","List2","List3"},0)

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Gavona]

Good one Ken!
If the fourth multiplier was 4, not 3 then

=CHOOSE(1+COUNTIF(RESI,C28)*1+COUNTIF(COMM,C28)*2+COUNTIF(OTHER,C28)*[red]4[/red],{"Not Found","List1","List2","Lists 1&2","List3","Lists 1&3","Lists 2&3","Lists 1,2&3"},0)

Only works of no morethan one occurence in each list.

Regards,


Gavin

 

[Kevinski]

Thanks a lot guys - really helpful. The whole VLOOKUP concept gelled with me after reading this and I converted everything into 1 lookup list.

I have now recorded a macro as below based on the test data I'm using that does everything I want and the only issue I have now is determining how many rows to perform the VLOOKUP on based on a the varying number of records I'm dealing with each week.

I know it's some kind of loop but I can't work out the syntax. Here's the VB for the macro as it stands
___________________________________________________
Sub Counttypes()
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B2").Select
Range("A1:I10").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],TLIST,2,FALSE)"
Selection.AutoFill Destination:=Range("C2:C10")
Range("C2:C10").Select
Range("C2").Select
Range("C2").Select Range("A1:J10").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Subtotal GroupBy:=3, Function:=xlCount, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
_________________________________________________

Basically I just did an "autofill" based on the number of rows of data I had - but what's the syntax for "keep doing this until you run out of records" and where do I insert it (and what do I delete) in the script above?

Cheers

 

[GlennUK]

You need to find the last used row, and there are many ways to do that. If you look in the VBA Visual Basic For Applications Forum you will find lots of examples, and also look in the FAQ, such as:

http://www.tek-tips.com/faqs.cfm?fid=2112

In fact if you are going to be asking VBA questions you should post a new thread in that forum.

When you have the last used row, then alter your code so that the 10 you currently have is replaced by the variable containing the last row. For example:
Range("A1:I10").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
becomes
Range("A1:I" & LastRow).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

and

Selection.AutoFill Destination:=Range("C2:C10")
becomes
Selection.AutoFill Destination:=Range("C2:C" & LastRow )

and

Range("A1:J10").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
becomes
Range("A1:J" & LastRow).Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Hope that helps.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Gavona]

Also, immediately after you have autofilled all the cells containing your formula will be selected. You may want to copy this selection and then pastespecial values (Edit,copy,pastespecial,values).

Finally your code could be made more efficient. The recorder is great but generally its a good idea to simplify the code and make it more readable. eg your first three lines reduce to:
Rows("2:2").Delete




Gavin