VLOOKUP Function

[esgca2010]

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:

[TABLE="width: 714"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD] 4 [/TD]
[TD] 5[/TD]
[TD] 6[/TD]
[/TR]
[TR]
[TD]Item No.[/TD]
[TD]Product[/TD]
[TD]Unit
Price[/TD]
[TD] Quantity In Stock [/TD]
[TD] MSRP[/TD]
[TD] Store #[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]Sony CD Player[/TD]
[TD] $350.00[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]$495[/TD]
[TD="align: right"]1011[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]Apple Ipod[/TD]
[TD] $375.00[/TD]
[TD="align: right"]1478[/TD]
[TD="align: right"]603[/TD]
[TD="align: right"]1012[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]Sony 20" Monitor[/TD]
[TD] $650.00[/TD]
[TD="align: right"]4562[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]1013[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]HP Printer[/TD]
[TD] $250.00[/TD]
[TD="align: right"]1597[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]1014[/TD]
[/TR]
[TR]
[TD]555[/TD]
[TD]Nikon Digital Camera[/TD]
[TD] $895.00[/TD]
[TD="align: right"]2456[/TD]
[TD="align: right"]1025[/TD]
[TD="align: right"]1015[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your support.

Elaine

 

[Jeffrey Mahoney]

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

 

[esgca2010]

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

 

[Aladin Akyurek]

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

Book1
	A	B	C	D	E	F
1	Item No.	Product	Unit Price	Quantity In Stock	MSRP	Store #
2	111	Sony CD Player	$350.00	1002	$495	1011
3	222	Apple Ipod	$375.00	1478	603	1012
4	333	Sony 20" Monitor	$650.00	4562	750	1013
5	444	HP Printer	$250.00	1597	325	1014
6	555	Nikon Digital Camera	$895.00	2456	1025	1015
Sheet2

Book1
	A	B	C
1	Item No.	Product	MSRP
2	333	Sony 20" Monitor	750
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.

 

[esgca2010]

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

 

[esgca2010]

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

 

[Aladin Akyurek]

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

You are welcome.