Altered VLOOKUP Function

[ngan22]

I can do VLOOKUP's from different sheets in a workbook,

=VLOOKUP(A4,Import,7,FALSE)

but if the number I want to have as a result is in a different row, ie:

Prod ID
11100
999

Where (xxxxx) is "A2" and (xxx) is in cell "C3", how can I tell the formula to look in column 3 and down one row from the lookup value (A2).

I am working from production reports that can't be altered, so the source data cannot be changed.

Thanks

 

[MrActuary]

Is the value you want always 2 columsn over and 1 column down from the lookup value or does this vary by case?

 

[just_jon]

A bit more detail would help, as well as the answer to the question posed above.

 

[ngan22]

I can't get the HTML Maker Utility to install for some reason, so I will try to explain in words.

For Instance:

The "Lookup Value" is A1
The "Table Array" is not a problem.
The "Col Index Number" is always 3

The only problem is that the value in column 3 is down one row.

So, if the lookup value is in A1, the target value is in C3.

It is a little more complicated than this, but if I can get this simplified version to work, I can explode it out to the rest of the worksheets.

Thanks alot for your help.

 

[ngan22]

Sorry, I guess the target result would be in C2 in that example above...

Kind of hard to help a fella when he can't get his question right, eh?

 

[just_jon]

Something like this?

Book1
	A	B	C	D	E	F
1	Field1	Field2	Field3		CCC	D1D
2	AAA	123	A1A
3	BBB	456	B1B
4	CCC	789	C1C
5	DDD	1122	D1D
6	EEE	1455	E1E
7
Sheet3

 

[ngan22]

If this example above was my spreadsheet, and my "Lookup Value" was AAA, I want the result of:



If A1 on my sheet was "AAA"

=VLOOKUP(A1,table,2,FALSE) to be = 456

Currently, this formula would give me = 123

Can you tell the formula to look at the next row?

 

[just_jon]

Book1
	A	B	C	D	E	F
1	Field1	Field2	Field3		AAA	456
2	AAA	123	A1A
3	BBB	456	B1B
4	CCC	789	C1C
5	DDD	1122	D1D
6	EEE	1455	E1E
7
Sheet3

INDEX/MATCH performs as a VLOOKUP, with the added benefits of being able to look to the left, offset rows, etc.

 

[ngan22]

Thank you. It wasn't a VLOOKUP at all!