MATCH working but not....

[Hile]

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:E787,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.

 

[Max]

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

 

[Dave Peterson]

Column E of Area is text.

Is Column G of Survey text or numbers formatted to look like that (leading 0)?

If there's exactly one match for each item in Survey!G:G
=vlookup(g1&"*",area!e:f,2,false)
or
=vlookup(text(g1,"000000")&"*",area!e:f,2,false)

You may want to check for an empty cell:
=if(g1="","",vlookup(g1&"*",area!e:f,2,false))

If there are multiple entries for each of those numbers in column G of Survey:
=sumif(Area!E:E,TEXT(G1,"000000")&"*",Area!F:F)
or
=sumif(Area!E:E,G1&"*",Area!F:F)

Both =vlookup() and =sumif() are functions that will accept wildcards (the
asterisk in this case).

======
Actually, if the values are really text, then using the =text() function won't
hurt--but it won't help, either.

 

[Hile]

Max
Thanks, this did not work however. It would not find a match in the array,
even though one existed. I think because of the name associated with the
number in the cell. Dave's introduction of the wildcard seem to have done the
trick. Though I did not try entering wildcards in your formula.

 

[Hile]

Thank you so much. In the interest of information sharing and learning here
goes a long post....

I successfully used your vlookup (=VLOOKUP(G7&"*",Area!$E$5:$F$787,2,FALSE);
I tried using the blank cell check, but then it wouldn't work and since I
knew that my dataset had no blank cells I gave up on that alltogether. As far
as formatting goes, all cells were formatted to general and column G was
really a results formula to normalize the fact that in the survey it was a
text field users can enter whatever they wish though we only have 6 digit
location numbers (don't ask...). So column G's formatting looked like this
where column F contained the survey data:
=IF($F7<999,IF($F7<99,"00"&$F7&"10","0"&$F7&"10"),IF(AND($F7>1000,RIGHT($F7,2)<>"10"),$F7&"10",TEXT($F7,"000000"))) which worked for 99.99% of entries.

I could have used your multiple entries formula against my location master
raw data which counted each title within a location. However, I had already
done a pivot to summarize this data at location level which is what the Area!
tab was. But I'll keep this one on my reference file for future use!!!

The reason I didn't initially use the vlookup is because I thought vlookup
didn't work unless all your data was on the same tab.

If you have the time, the inclination, and the answer - can you tell me why
my formula was not working? Why could I see the correct row position in the
dialog box but not get that answer in the cell on the spreadsheet. Each
component of my formula worked on its own but not when they were put
together. I thought it was that the array contained both number and text, but
then it would not have worked on the insert formula dialog box either. I'm
completely perplexed.

Thanks again though, you saved me hours of work and I'll be able to meet my
deadline!

 

[Max]

Thanks for feeding back. The earlier expression looks for an exact match. It
presumes that you have 6-digit text numbers in G2 down (the lookup numbers).
If these are actually real numbers formatted to look like text, then you
could use: TEXT(G2,"000000") to convert it, so that it'll match with the text
numbers returned by LEFT in: LEFT(Area!E$5:E$787,6)

You might want to try array-entering* in F2:
=IF(ISNA(MATCH(TRUE,TEXT(G2,"000000")=LEFT(Area!E$5:E$787,6),0)),"",INDEX(Area!F$5:F$787,MATCH(TRUE,TEXT(G2,"000000")=LEFT(Area!E$5:E$787,6),0)))
Then copy F2 down.

*Visually check that the formula is wrapped by curly braces: { } after you
press CTRL+SHIFT+ENTER to array-enter the formula in F2 (look in F2's formula
bar). If you don't see the curlies, that means you didn't array-enter it
properly. Click inside the formula bar, re-do the CTRL+SHIFT+ENTER.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik

 

[Max]

why my formula was not working? ..

I believe it's just the simple fact that you did not array-enter your
formula -- its an array formula -- ie you did not press CTRL+SHIFT+ENTER to
confirm the formula

You should visually check that the formula is wrapped by curly braces: { }
after you
press CTRL+SHIFT+ENTER to array-enter the formula in the cell (look in the
cell's formula bar). If you don't see the curlies, that means you didn't
array-enter it
properly. Click inside the formula bar, re-do the CTRL+SHIFT+ENTER.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---