# Altered VLOOKUP Function

#### ngan22

##### Board Regular
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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Is the value you want always 2 columsn over and 1 column down from the lookup value or does this vary by case?

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

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.

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?

Something like this?
Book1
ABCDEF
1Field1Field2Field3CCCD1D
2AAA123A1A
3BBB456B1B
4CCC789C1C
5DDD1122D1D
6EEE1455E1E
7
Sheet3

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?

Book1
ABCDEF
1Field1Field2Field3AAA456
2AAA123A1A
3BBB456B1B
4CCC789C1C
5DDD1122D1D
6EEE1455E1E
7
Sheet3

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

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

Replies
22
Views
354
Replies
10
Views
609
Replies
3
Views
357
Replies
1
Views
274
Replies
0
Views
251

1,206,949
Messages
6,075,810
Members
446,158
Latest member
octagonalowl

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

### Which adblocker are you using?

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

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