Sunday, August 31, 2014

yogi_Extract Information For Specified Street Numbers From A Table Of Odd And Even Street Name Ranges

                      Google Spreadsheet   Post  #1745
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-31-2014
post by Emanuel Borg:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/HzGrIBPuNl8)
Multiple index/match/lookup ? - Simple idea, but I can't find a solution
Hi, I've reached my Spreadsheets knowledge limit. Does anyone have an idea what kind of formula I could use to extract the correct Post code (column D) based on the Street name (column A) and whether the street number is odd/even and between the numbers in columns B and C. See exemple below:

Thanks a lot for any tips!
/Manne
---
hank you for all your replies!


Perhaps I explain myself better here. Here is the link to an example document: https://docs.google.com/spreadsheets/d/1z8Ea3KwzQ1viBdwUbwVPRtls1Y1YeTZx4S1PEYv7G88/edit?usp=sharing

James, you are absolutely right! This is my goal:
I think I understand what Emanuel is wanting. Looking at the sample data take the number 27 in cell B10 where it is an odd number and is between the odd range of 11 to 115, so the code to pull is 123 46.

The code suggestions that I received looks great, I they seem to work, however the range to search is huge, and I have to have an automated formula which uses the existing data to pull correct information.

In Sheet2, I would love to VLOOKUP(A2,Sheet1!A1:E7,4,0) if and only if B2 >= the vlookup's same row's B column, AND B2 <= the vlookup's same row's C column... if this is even possible.


Does this make any sense to you? It's difficult to formulate my problem... *cough* Please have look at the example.


Thanks for your patience,
Manne
-------------------------------------------------------------------------------------------------------------------------------