Vlookup can be used and you dont need the array for that (but will
need it for the match). I like to lock down the Vlookup formula ($A2:A
$99999) You could run into a problem when you if u have duplicate
data. If possible give these unique id's if these are order numbers -
1,2,3,4 (or concatenate the known unique information that is shared
and lookup that value). You can nest the lookup inside of IF
statements. Your selection of function is somewhat dependent how the
sheets are structured and what information you want.
On Aug 25, 9:34*am, 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,Cmatchon 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 I’m 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 nomatchbetween 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 tomatch--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)matchA2 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 onematchand you're bringing back a number (or 0 if there is
nomatchfor all the criteria), you can use:
=sumproduct(--(othersheet!a1:a10=a1),
* * * * * * --(othersheet!b1:b10=b1),
* * * * * * * (othersheet!c1:c10))
Adjust the ranges tomatch--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 canmatch2 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