Match, Index or Vlookup

[srizki]

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

 

[NBVC]

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]

Thanks,
tables are on the same sheet.

 

[barry houdini]

Perhaps just VLOOKUP.....

Book1
	A	B	C	D	E	F
1	banana	2		grapefruit	1
2	apple	#N/A		banana	2
3	grape	6		banana	3
4				grapefruit	4
5				grapefruit	5
6				grape	6
7				banana	7
8
Sheet1

formula in B1 copied down

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

 

[srizki]

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]

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]

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.