Vlookups

[agerrard]

Hi All,

Does anyone know how to do a vlookup, but then to take the value on either side of the cell?

So say i have the following:

A B C D
1 26.49 19.99 26.49
2
3 Product 1 1000 8000 700

So i want the vlookup to find the $19.99 value (so in C1) but instead of returning the 8000 value i want it to take the 1000 value instead (so the one to the left).

Is this possible or is their another way to do this?

Thanks!

 

[T. Valko]

Hi All,

Does anyone know how to do a vlookup, but then to take the value on either side of the cell?

So say i have the following:

A B C D
1 26.49 19.99 26.49
2
3 Product 1 1000 8000 700

So i want the vlookup to find the $19.99 value (so in C1) but instead of returning the 8000 value i want it to take the 1000 value instead (so the one to the left).

Is this possible or is their another way to do this?

Thanks!

It's not real clear what you want to do.

If you want to do a lookup to the left...

Book1
	A	B	C	D	E
2	90	A		Lookup	Result
3	34	B	_	D	65
4	45	C	_	_	_
5	65	D	_	_	_
6	94	E	_	_	_
7	62	F	_	_	_
8	50	G	_	_	_
9	13	H	_	_	_
10	25	I	_	_	_
Sheet1

Formula entered in E3:

=INDEX(A2:A10,MATCH(D3,B2:B10,0))

 

[Marcelo Branco]

Hi,

You can use INDEX/MATCH. Say F1 houses the lookup-value (19.99 in the example), try this on Excel 2007 or higher

=IFERROR(INDEX(B3:D3,MATCH($F$1,$B$1:$D$1,0)-1),"")

HTH

M.