In Survey,

Assuming data in G2 down
Array-enter (press CTRL+SHIFT+ENTER to confirm the formula)
this expression into F2:
=IF(ISNA(MATCH(TRUE,G2=LEFT(Area!E$5:E$787,6),0)), "",INDEX(Area!F$5:F$787,MATCH(TRUE,G2=LEFT(Area!E$ 5:E$787,6),0)))
then copy F2 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---
"Hile" wrote:
I have a survey results spreadsheet that I need to add headcount to based on
location number. I have the survey's location # in column G (Survey!), I need
to find that location number on column E in a different tab (Area!) and
return the headcount from that tab on column F to column I in Survey! tab.

Survey!G:G (200 records, sort always changes depending on what we look at)
060210
007810
060610
007110

Area!E:F (700+ records sorted ascending)
001010.ADAMSVILLE 12
001110.ALLENTOWN 26
001210.COLUMBUS EAST 19
001610.ANDERSON 5
007110.CARSON 62
007410.CHATTANOOGA 29
007810.CINCINNATI 59


I tried this formula which is not working:
=IF(ISNA(INDEX(Area!$E$5:$F$787,MATCH(LEFT(Survey! G7,4),LEFT(Area!E5:E787,4),0),2)),"",INDEX(Area!$E $5:$F$787,MATCH(LEFT(Survey!G7,4),LEFT(Area!E5:E78 7,4),0),2))

So I first checked my MATCH formula to see if that was the problem. But when
I just try the MATCH and open up the "insert function" dialog box, I can see
that the formula is giving me position 270 which is the exact match of G7,
however on the cell itself it says #NA. I tried changing the formatting of
the cell to see if that would help to no avail. I don't know how to fix
something that is working but not showing up on the cell. :-)

Any help will be greatly appreciated. Maybe I'm missing something or have
something I shouldn't, but I can't tell. I've looked at it for hours.
--
Hile