esgca2010

New Member
Joined
Jul 29, 2010
Messages
31
Is there a way to have the column index number find it for you without having to count? If I have 50 columns and in the first Vlookup the column index is 10, in the second Vlookup the column index is 40.

Example:

123 4 5 6
Item No.ProductUnit
Price
Quantity In Stock MSRP Store #
111Sony CD Player $350.001002$4951011
222Apple Ipod $375.0014786031012
333Sony 20" Monitor $650.0045627501013
444HP Printer $250.0015973251014
555Nikon Digital Camera $895.00245610251015

<colgroup><col span="6"></colgroup><tbody>
</tbody>

Thank you for your support.

Elaine
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
You didn't specify which value you wanted to return. Let's use MSRP. Use MATCH to find the column by the Header Name. In the example below, A2 is the lookup value which could be Item No. Table1 is a named range for the table you want to pull the data (all rows and columns). Table1_hdrs is as named range that includes only the headers at the top of Table1.

=VLOOKUP(A2,Table1,MATCH("MSRP",Table1_hdrs,0),FALSE)

Jeff
 
Upvote 0
Hello Jeff,

My apologies for not being clear.

The example I provided is a list of products in sheet 2. In sheet 1 cell A2, the user will type an item number, in cell B2 VLookup function retrieves that information based on the column index number which could be column index number 31.

When the VLOOKUP function is created, I do not want to go to the "List of products" sheet to count manually the column index to use. If the product list has 50 columns or more and in VLOOKUP I want to use column index number 31, I do not want to have to count manually. What other function would I have to use with VLOOKUP to accomplish this goal?

I thank you in advance for your patience and support.

Elaine
 
Upvote 0
Jeff's suggestion should work. The following is a generalized version.


Book1
ABCDEF
1Item No.ProductUnit PriceQuantity In StockMSRPStore #
2111Sony CD Player$350.001002$4951011
3222Apple Ipod$375.0014786031012
4333Sony 20" Monitor$650.0045627501013
5444HP Printer$250.0015973251014
6555Nikon Digital Camera$895.00245610251015
Sheet2



Book1
ABC
1Item No.ProductMSRP
2333Sony 20" Monitor750
Sheet1


The user entries/specifications are in black (bold).

In B2 of Sheet1 enter and copy across (and down for more items)…

=VLOOKUP($A2,Sheet2!$A:$F,MATCH(B$1,INDEX(Sheet2!$A:$F,1,0),0))


Note. B1 and C1 can contain dropdown list from which the user can choose a result type.
 
Upvote 0
Hello Jeffrey,

I can't begin to express my appreciation for your support. You are AWESOME!!!

It works like a champ!

From the bottom of my heart, thank you!

Elaine:)
 
Upvote 0
Hello Aladin,

I just want to say thank you! Your formula works like a champ!

I humbly thank you for your support.

You are the BEST!!!

Regards,
Elaine
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top