The original formula that I suggested didn't have those []'s. But Meredith was
trying to retrieve data from a worksheet in a different workbook. The name of
that (already opened) workbook is in those square brackets []'s.
And I'm gonna guess that you are suffering from the same problem that Meredith
has. Your data doesn't really match. There is no single row in Sheet2 that
matches A2 in A2:A101, b2 in B2:B101 and C2 in C2:C101.
Try an experiment.
Insert a new row (say row 10) in that Sheet2 (the table sheet).
Put these values in A10, B10, C10:
asdf
qwer
zxcv
Then put those same values in the sheet with the formula (in A2:C2). I bet your
formula (still array entered) works perfectly.
So your job will be to find out why those values don't match--even when they
look like they match to you.
Look for leading/trailing spaces. Typos... and check Debra's site, too:
Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble
(And it'll apply to =match(), too.)
catts22 wrote:
Hi - I have a similar question...
I have 3 columns on Sheet 1 and need to have the result in the fourth column
based on information on Sheet 2.
Sheet 1
A B C D
1 State City Address Company
2 AL Birming ALL
3 NC All ALL
4 NY New York 123 X St
5 NY Long City 999 A St
A B C D
1 State City Address Company
2 AL Birming ALL Yellow
3 NC All ALL Yellow
4 NY New York 123 X St Red
5 NY Long City 999 A St Blue
6 CT ALL ALL Red
7 GA ATLANTA 5TH St Red
In D2 on Sheet 1 - I tried this formula with the idea that I need the
company name shown on column D of Sheet 2 if Column A,B,C match on both sheets
=INDEX('Sheet 2'!D2:D101,MATCH(1,(a2='Sheet 2'!A2:A101)*(b2='Sheet
2'!B2:B101)*(c2='Sheet 2'!C2:C101),0))
I keep getting # N/A -
I read that you must use control shift enter to get curly brackets €“ when I
did this I got {=INDEX('Sheet 2'!D2:D101,MATCH(1,(a2='Sheet
2'!A2:A101)*(b2='Sheet 2'!B2:B101)*(c2='Sheet 2'!C2:C101),0))} - the result
was still #NA
Your formula example has [ ] brackets, so now Im very confused.
Please help.
"Dave Peterson" wrote:
=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,
MATCH(1,A5='[WIP - August 19.xls]19-Aug-2009'!$E$2:$E$3873)
*(B5='[WIP - August 19.xls]19-Aug-2009'!$G$2:$G$3873),0)
I'm gonna guess that there is no match between the value in A5 and E2:E3873 and
at the same time between the value in B5 and G2:G3873.
Just like =vlookup() or =match() will return an error.
Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble
(And it'll apply to =match(), too.)
If that doesn't help, what are you matching on--simple text, whole numbers,
times, dates, fractions, long strings????
Meredith wrote:
Hi Dave,
I tried to enter this
=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,MATCH(1,A5='[WIP -
August 19.xls]19-Aug-2009'!$E$2:$E$3873)*(B5='[WIP - August
19.xls]19-Aug-2009'!$G$2:$G$3873),0) using the ctrl+shift+enter functions.
It came back with an #NA error. Can you please try to diagnose the formula
and let me know where you think it went wrong.
Thanks,
"Dave Peterson" wrote:
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a
third), you could use:
=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))
(all in one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
Adjust the range to match--but you can only use the whole column in xl2007.
This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.
And you can add more conditions by just adding more stuff to that product
portion of the formula:
=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))
============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:
=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))
Adjust the ranges to match--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Meredith wrote:
Hi,
I need a VLOOKUP which can match 2 criteria.
I have 2 spreadsheets which each contain: Client No. and Engagement
Description. They are the same on both spreadsheets, however, I am doing the
VLOOKUP to find the Budget for that specific Client No. and Engagement
Description.
Is there a way to get the VLOOKUP or any other formula to work?
Thank you
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson