Match, Index or Vlookup

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
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

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

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks,
tables are on the same sheet.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

srizki

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

srizki

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

Forum statistics

Threads
1,136,504
Messages
5,676,254
Members
419,616
Latest member
quickflip

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
Top