# Match, Index or Vlookup

#### srizki

##### Well-known Member
I have two tables, I need to MATCH column A of table one to column D of table two, and if it matches get the value from column E of table two.

Thanks

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try

=Index(\$E\$1:\$E\$100,Match(A1,\$D\$1:\$D\$100,0))

or

=Vlookup(A1,\$D\$1:\$E\$100,2,0)

if the tables are in different sheets, you need to include sheetname in front of the ranges..

Thanks,
tables are on the same sheet.

Perhaps just VLOOKUP.....
Book1
ABCDEF
1banana2grapefruit1
2apple#N/Abanana2
3grape6banana3
4grapefruit4
5grapefruit5
6grape6
7banana7
8
Sheet1

formula in B1 copied down

=VLOOKUP(A1,D\$1:E\$7,2,0)

Since tables are on the same sheet I can not use Vlookup
so far I used
=IF(ISNA(VLOOKUP(\$A560,\$D\$5:\$E\$455,2,0)),0,(VLOOKUP(\$A560,\$D\$5:\$E\$455,2,0))). but now I need to take only the values that are combine in both and reject that are not. So if the amount in both tables is same for the given ID, or if one has the amount and other does not again for the same ID. I do not want it, I only need in both table have an amount for the same ID and the amounts are different.
I used =SUMIF(G4:G575,"=0",H4:H575) and
=SUMIF(G4:G575,"<>0",H4:H575) to sum of same or no values.
But doing all that I have \$60K difference, so I dicided to use Match Index formula.

Since tables are on the same sheet I can not use Vlookup
so far I used
=IF(ISNA(VLOOKUP(\$A560,\$D\$5:\$E\$455,2,0)),0,(VLOOKUP(\$A560,\$D\$5:\$E\$455,2,0))). but now I need to take only the values that are combine in both and reject that are not. So if the amount in both tables is same for the given ID, or if one has the amount and other does not again for the same ID. I do not want it, I only need in both table have an amount for the same ID and the amounts are different.
I used =SUMIF(G4:G575,"=0",H4:H575) and
=SUMIF(G4:G575,"<>0",H4:H575) to sum of same or no values.
But doing all that I still have \$60K difference, so I dicided to use Match Index formula.

Since tables are on the same sheet I can not use Vlookup
so far I used
=IF(ISNA(VLOOKUP(\$A560,\$D\$5:\$E\$455,2,0)),0,(VLOOKUP(\$A560,\$D\$5:\$E\$455,2,0))). but now I need to take only the values that are combine in both and reject that are not. So if the amount in both tables is same for the given ID, or if one has the amount and other does not again for the same ID. I do not want it, I only need in both table have an amount for the same ID and the amounts are different.
I used =SUMIF(G4:G575,"=0",H4:H575) and
=SUMIF(G4:G575,"<>0",H4:H575) to sum of same or no values.
But doing all that I still have \$60K difference, so I dicided to use Match Index formula.

Replies
6
Views
201
Replies
6
Views
681
Replies
3
Views
224
Replies
1
Views
413
Replies
1
Views
150

1,218,746
Messages
6,144,258
Members
450,533
Latest member
xoxo1998

### 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.

### Which adblocker are you using?

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