What to do when VLOOKUP range is expanded

[kualjo]

I have a series of VLOOKUP formulas in a single column of one worksheet that pull in data from multiple columns on another worksheet. If I have to add a new column(s) into the source worksheet, the VLOOKUP formula column references on the destination worksheet will automatically change to accommodate the addition(s), but the column index number does not. The result is that my table array grows, but the index number doesn't. For example:

=VLOOKUP(O184,Sheet1!$B:I,8,FALSE)

If six columns are inserted before column I, the table array changes to $B:O, but the column index will remain at 8 when I need it to advance to 14. What, if anything, can I do to automate a change in the column index number?

 

[Jonmo1]

use Index/Match instead

for your example, that would be

=INDEX(I:I,MATCH(O184,Sheet1!$B:$B,0))


Hope that helps.

 

[kualjo]

Actually, I considered that (though I'm not too strong with INDEX and MATCH), but the column B that I am referencing is a sequence of dates, and the information in the other columns on Sheet1 are production volumes for different models. When I use the formula you suggested, it pulls in the sum total of all production for the specific date in column O. I only need it to pull that one model's production for that date.
It's kind of hard to explain the layout of the worksheets, but basically I have multiple columns on the source worksheet that I'm "stacking up" on the destination worksheet.

 

[Jonmo1]

Not sure I understand the problem...
I did leave off the sheet reference for column I...

This
=INDEX(Sheet1!I:I,MATCH(O184,Sheet1!$B:$B,0))

Will have the EXACT same result as
=VLOOKUP(O184,Sheet1!$B:I,8,FALSE)


If you insert a column between B and I
the vlookup changes to
=VLOOKUP(O184,Sheet1!$B:J,8,FALSE) - still referring to column I (8)

But the Index/Match changes to

=INDEX(Sheet1!J:J,MATCH(O184,Sheet1!$B:$B,0)) - now referring to column J

 

[kualjo]

Ah yes! I see it now. That works! Thanks jonmo!
(I really need to work on those two functions.)