How to add a cell value to VLOOKUP?

[engel59]

In my "Main Board Parts" tab I use the following in one of my cells:
=IF(ISERROR(VLOOKUP(TRIM($H34),Inventory!$M$2:$W$1350,11,0)),"Not
Found",VLOOKUP(TRIM($H34),Inventory!$M$2:$W$1350,11,0))
A "Not Found" message is returned if the number I'm looking for does not
exist.
I also use the Trim feature to make sure there are no spaces in the text I'm
looking for.

H34 is a number, in my current workbook tab (Main Board Parts) I want to
look up in my workbook's "Inventory" tab.
M2 thru W1350 are the rows of data I look thru so I can find what I'm
looking for in my main Inventory worksheet tab.

Here is what I would like to be able to do; when I add more columns to my
inventory tab, my VLOOKUP doesn't always update the W1350 value in the
formula string.
I would like to have two cells that have the values M2 and the W1350.
Now when I add more columns I don't have to go thru and re-modify each
formula throught out the workbook.
To give you a rough idea on how big my workbook is; the inventory page is
~50 rows wide and over 1350 columns long. I have over 30 tabs and the tabs
range from 10 rows to close to 100 rows long each.

Here is what I want it to look like:
=IF(ISERROR(VLOOKUP(TRIM($H34),Inventory!$M"Formula!A1":$W"Formula!A2",11,0)),"Not
Found",VLOOKUP(TRIM($H34),Inventory!$M$"Formula!A1":$W$"Formula!A2",11,0))

In the "Formula" tab:
A1 = 2
A2 = 1350

How can I make this work?

 

[engel59]

Thanks for the quick reply.
If I placed A1 and A2 under tab Formula would the formula look like:

=IF(ISERROR(VLOOKUP(TRIM($H34),INDIRECT(Inventory!$M"&Formula!A1&":$W"&Formula!A2",11,0)),"Not
Found",VLOOKUP(TRIM($H34),INDIRECT(Inventory!$M"&Formula!G1&":$W"&Formula!A2",11,0))

Would this be correct or do I have too many double quotes (") or ands (&)?
Do I need the dallor sign to lock it to that cell?
I would be doing a cut and paste down the row of the other cells.

 

[Max]

You would need to fix the points to A1 & A2 in the INDIRECT, since the concat
string is supposed to resolve to the table array, which is meant to be fixed
for propagation purposes.

In your expression, try amending your table arrays:
INDIRECT(Inventory!$M"&Formula!A1&":$W"&Formula!A2"
INDIRECT(Inventory!$M"&Formula!G1&":$W"&Formula!A2"

to these:
=INDIRECT("Inventory!$M"&Formula!$A$1&":$W"&Formula!$A$2)
=INDIRECT("Inventory!$M"&Formula!$G$1&":$W"&Formula!$A$2)

It should work fine
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[engel59]

Thank you, I'll give it a try.

You would need to fix the points to A1 & A2 in the INDIRECT, since the concat
string is supposed to resolve to the table array, which is meant to be fixed
for propagation purposes.

In your expression, try amending your table arrays:
INDIRECT(Inventory!$M"&Formula!A1&":$W"&Formula!A2"
INDIRECT(Inventory!$M"&Formula!G1&":$W"&Formula!A2"

to these:
=INDIRECT("Inventory!$M"&Formula!$A$1&":$W"&Formula!$A$2)
=INDIRECT("Inventory!$M"&Formula!$G$1&":$W"&Formula!$A$2)

It should work fine
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[engel59]

Max,
Thank you so much...got it to work:

=IF(ISERROR(VLOOKUP(TRIM($H22),INDIRECT("Inventory!$BF"&'Main Index'!$G$1&"
: $BG" &'Main Index'!$G$2),2,0)),"Not
Found",VLOOKUP(TRIM($H22),INDIRECT("Inventory!$BF"&'Main Index'!$G$1&" : $BG"
&'Main Index'!$G$2),2,0))

Inventory is where I get my info
Main Index is where I placed my VLOOKUP top and bottom values.

Question:
Is the and sign (&) used to concatinate?
Why are ther only 3 double-quotes (") used?
INDIRECT("Inventory!$BF"&'Main Index'!$G$1&" : $BG" &'Main Index'!$G$2)

 

[Max]

Max,
Thank you so much...got it to work

Glad to hear. Celebrate your success, click the YES button in that response

Is the and sign (&) used to concatenate?

Yes, its a much shorter way, instead of using CONCATENATE

Why are there only 3 double-quotes (") used?
INDIRECT("Inventory!$BF"&'Main Index'!$G$1&" : $BG" &'Main Index'!$G$2)

If you look closely, there are actually 4 double quotes (2 pairs)
involved in your exp above, viz:

"Inventory!$BF"
" : $BG"

It must be in pairs. Whatever is within the double quotes
are just static text strings.

And if above helps, pl click the YES button below as well.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---