if match????

maddyado

New Member
Joined
Sep 25, 2006
Messages
7
I'm trying to match two or more items from another spreadsheet onto another, then if both items are found, return another column from the same spreadsheet. For example:


ORIGINAL SPREADSHEET:
A B C
1 cat 2 y
2 dog 3 x
3 pig 1 z
4 owl 0 a

NEW SPREADSHEET
A B C
1 2 y
2 3 x
3 1 z
4 0 a

so basically, i'm trying to fill in column a automaticaly by linking it somehow to the original spreadsheet without re-typing the whole thing again! My current isn't quite perfect but here's what I have:

=IF(MATCH(B1,Original SpreadhseetB1:B4), MATCH(C1,Original SpreadshetC1:C4,0))

The problem with the above formula is it's returning the line where it's located in the original spreadsheet rather than the text.

Can you help me please???

thanks!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
Just a shot in the dark!

I a relative rookie but this may help

You need to Index INDEX($B1:$B4,MATCH(B1,B1:B4,)),0)

use this theory LOOKUP(lookup_value,lookup_vector,result_vector)

You need to stipulate your result vector Match only returns the position of a relative cell when a match is found

INDEX($B1:$B4,MATCH(B1,B1:B4,)) this is an example of your lookup vector

Please correct me...(anyone) if I'm off base

Hope it helps
 

maddyado

New Member
Joined
Sep 25, 2006
Messages
7
if match???

Thank you for the reply. However, I did try the INDEX formula last night. The only problem with this formula is it looks horizontally and vertically. My orginal spreadsheet is straight across information and they do not line up exactly as in what's in row 10 in the orig. spreadsheet isn't necessarly the same in row 10 in the new spreadsheet, otherwise, I could've used the IF(AND.....) formula.

Help??
 

BruceyBonu$

Board Regular
Joined
Mar 6, 2006
Messages
74
To match more than one column at a time try the following;

=INDEX(Original Spreadsheet!$A1:$c4,MATCH($a1&"@"&$b1,Original Spreadsheet!$A1:$a4$&"@"&original Spreadsheet!$B1$:$B4$,0),1)
enter the formula with Ctrl-Shift and Enter in cell a1
This should appear with Curly braces around {}
 

maddyado

New Member
Joined
Sep 25, 2006
Messages
7

ADVERTISEMENT

if match???

Hello,

I tried copy and pasting that formula onto the spreadsheet I'm working on but I'm getting an error message!!!!

Here's what I did:

=INDEX(orig sheet A1:C4, MATCH(new sheet B1&@&C2,orig sheet B1:B4&"@"orig sheet C1:C4,0),1)

What am I doing wrong??

ARRRRGGGHHHHH!!!
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
Don't let this stuff get to ya...patience is the best thing cause there are always constant problem solving situations

I noticed a formula error in bold!


=INDEX(orig sheet A1:C4, MATCH(new sheet B1&"@"&C2,orig sheet B1:B4&"@"orig sheet C1:C4,0),1)
 

BruceyBonu$

Board Regular
Joined
Mar 6, 2006
Messages
74
Hi , apologies for any confusion... ensure that both worksheets are in the worksheet and that the worksheets are named 'OLD' and 'NEW' respectively. your data range ('OLD' will alomost certainly contain more info than 4 rows so please replace 4 with the bottom row number containing data)
do not enter the { and }, they will automatically go around your formula when you hit CTRL+SHIFT+ENTER

formula is
{=INDEX(Old!$A$1:$C$4,MATCH(New!A1&"@"&New!B1,Old!$A$1:$A$4&"@"&Old!$B$1:$B$4,0),1)}
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,687
Members
410,698
Latest member
Wloven
Top