A Combination Of The Two

Introduction

This is going to reference two pieces, one from Vocal Resources about Filters and one of mine about building a simple Vocal Network. At the end of this piece I will link the two stories and all the people I am aware of who have joined in with the Vocal Network that I suggested. I actually thought that Vocal would add it as a resource as they did comment favourably about it and it does bring Vocal members a little closer together, but we shall see if it ever gets picked up by Vocal.

Combinations: The Excel Example

We are often wary of combining seemingly disparate items, sometimes with good reason but I have to go back to an email that my boss sent me soon after joining my present company which effectively said “VLookup IS The Spawn Of The Devil”. The article recommended a combination of INDEX and MATCH which is both much faster and far more flexible than VLookup (we are in Microsoft Excel territory now, in case you were unaware) . I think it took me six months to actually get my head round it because combining two Excel commands just seems cumbersome and wrong. My youngest daughter Kirsty, still maintains that VLookup is the only way.

Below is my guide to this, cannot show full screenshots as it contains company informations but sure you will get the gist:

INDEX and MATCH Explained

Index returns the location of the information you want.

Match finds the location based on criteria.

Index and Match work on single columns or rows.

This means that you don’t need to bother about offsets or big data areas like you do with VLOOKUP and I assume HLOOKUP.

It also means that the search and results columns can be in any position. With VLOOKUP your search column is always to the left or your results column

In the example above (can’t show this) the requirement is to find the supplier based on Invoice number.

INDEX(B:B, Rownumber) B:B is the range that contains the supplier name now we add MATCH to find the row we want

MATCH(“141”,C:C,0) C:C is the range you are searching , and 0 means exact match (-1 nearest below, 1 nearest above)

So the final lookup is this:

INDEX(B:B, MATCH(“141”,C:C,0) )

In the above example names are looked up from another sheet. Below is the command that contains error checking with a look up to an alternate table with an illustration of the lookup table columns A/B being the preferred option and D/E if not found in cols A/B.

=IFERROR(INDEX('FLT Member'!$B:$B,MATCH(B6,'FLT Member'!$A:$A,0)),INDEX('FLT Member'!$E:$E,MATCH(LEFT(B6,1),'FLT Member'!$D:$D,0)))

Combinations: The Vocal Example

Apologies for the guide to Index and Match but if you use Excel it is an essential tool.

This is my 1102nd Vocal story and that means with my sieve like memory it is sometimes difficult for me to find stories of mine when someone asks me for them or I want to share them for some reason. I do wish there as was a Top Story filter, a way to see how many reads a story had by just selecting a story (you can see the hearts and comments but thats it) , but maybe they will turn up one day.

However by applying story filters you can limit the stories that you have to look though, and that is a hige help in finding things, though over four hundred of my stories are poems so that is a big section to trawl theough though not eleven hundred.

Now looking for other peoples stories. If you check muy profile or the image on my "At The Touch Of A Button" article you will see a list of my favourite Vocal Creators or Creationati as I have christened them so by a combination of this Vocal Network Method combined with filters means that I can find a creators story fairly quickly.

Obviously it is much more difficult for people finding my stories due to the sheer volumes, but this all helps in a small way.

Conclusion

This is the Vocal piece of Filters, The Vocal Network Users and The Vocal Network piece.

The Networkers I Know Of

Heather Hubler was the first one to take this up

Oneg

Loryne Andawey

It's a small start and if I have missed anyone let me know so I can add them.

Music is, appropriately, "Combination of the Two" by Big Brother and the Holding Company featuring Janis Joplin.