if match????

[maddyado]

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!

 

[seenfresh]

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]

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$]

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]

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]

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$]

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)}

 

[maddyado]

It worked!!! Woohooo!!!

Thank you so much.

Cheers,