Thursday, November 15, 2012

yogi_WorkAround For Using WildCard With use Of VLookup Function In Google Spreadsheet


                                          Google Spreadsheet   Post  #871
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 15, 2012
user Mark Howard said : (https://productforums.google.com/forum/#!mydiscussions/docs/kTh3kzymd-0)
Look up using partial data
So, I'm trying to use a variable length look up to return some data from a Sheet with data dumped from a form.

I can do it in Excel.

=(VLOOKUP((LEFT(D2,FIND(".",D2)-1))&"*",'Active Members'!$C$2:$F$2000,4,FALSE))

However, when using this formula in Google Sheet, this section:

(LEFT(D2,FIND(".",D2)-1))&"*"

is incorrectly assigning the "*" as an actual character, instead of a variable length string..(and ignoring anything post ".")

so while in Excel I might be looking up  mike.1234 and finding mike.4321
in Google sheets the formula is looking up mike* and not finding anything.

Is this gobbledygook? or does anyone understand what I'm doing, and are you able to help?

---
Hi Yogi,

here is a sample spreadsheet with the same set up as the "real" spreadsheet.

https://docs.google.com/spreadsheet/ccc?key=0AqnUolfHTBildEk5THlXRXFweGl0M1libnRLYXdLQUE

Data has been changed to protect those people, everything else should provide a good representation.

----------------------------------------------------------------------------------------
as of now Nov-15-2012) Google spreadsheet does not support use of Wildcard in use with VLookup function ...following is a workaround solution to the problem