Match, Index or Vlookup

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,851
Office Version
  1. 365
Platform
  1. Windows
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..

adjust ranges to suit.
 
Upvote 0
Thanks,
tables are on the same sheet.
 
Upvote 0
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)
 
Upvote 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.
 
Upvote 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 still have $60K difference, so I dicided to use Match Index formula.
 
Upvote 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 still have $60K difference, so I dicided to use Match Index formula.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top