if match????

New Member
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:

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

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:

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

thanks!

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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

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??

To match more than one column at a time try the following;

enter the formula with Ctrl-Shift and Enter in cell a1
This should appear with Curly braces around {}

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!!!

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)

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

It worked!!! Woohooo!!!

Thank you so much.

Cheers,

Replies
36
Views
1K
Replies
7
Views
215
Replies
3
Views
288
Replies
6
Views
272
Replies
4
Views
370

1,217,331
Messages
6,135,938
Members
449,973
Latest member
jarzack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back