Searching for simplicity...

[bobmc]

Hello gang...

I've noticed that if I use the VLOOKUP function with an existing table (this follows with HLOOKUP as well) as in Iteration 1 below, then modify my lookup table by inserting a column, the reference to the column returned in the VLOOKUP function does not update accordingly, which means I then have to manually modify the VLOOKUP formula.

My workaround is to use INDEX and MATCH as in Iteration 2 below. This allows me to cut and paste columns in the lookup table at will and the formulas still work. (As in Iteration 3, where the "COLOR" column was added after the fact.)

My question is whether or not there is a simpler, more elegent way (with shorter formulae) to recall relative return information from the lookup table while weathering the storm of lookup table revisions. I know I can use named ranges, but often the names of the ranges are longer than the cell callouts. In the spreadsheet I'm making, I'm also trying to make things as foolproof as possible which injects many IF's... For instance, the formula for cell D9 might read: =IF(C9="","",IF(INDEX(H9:H11,MATCH(C9,G9:G11,0),MATCH(D8,H8,0))="","Need item name",INDEX(H9:H11,MATCH(C9,G9:G11,0),MATCH(D8,H8,0))))

Any ideas on paring this down and still being able to insert columns or otherwise move lookup table information?

Thanks!

bobmc

Book1
	A	B	C	D	E	F	G	H	I	J
1	Iteration1		ITEMNO	ITEM	COST		ITEMNO	ITEM	COST
2			2	Orange	$0.55		1	Apple	$0.50
3							2	Orange	$0.55
4							3	Banana	$0.75
5
6
7
8	Iteration2		ITEMNO	ITEM	COST		ITEMNO	ITEM	COST
9			2	Orange	$0.55		1	Apple	$0.50
10							2	Orange	$0.55
11							3	Banana	$0.75
12
13
14
15	Iteration3		ITEMNO	ITEM	COST		ITEMNO	COLOR	ITEM	COST
16			2	Orange	$0.55		1	Red	Apple	$0.50
17							2	Orange	Orange	$0.55
18							3	Yellow	Banana	$0.75
Sheet1

 

[ken2step]

Cell D2
=VLOOKUP($C2,$G$1:$I$4,MATCH(D$1,$G$1:$I$1,0),FALSE)
Then fill over to Cell E2

Try this for your #1 example.....you can insert columns and rows with it.

 

[Aladin Akyurek]

Iteration is unconvincing.

1] If the lookup table is sorted in ascending order on ITEM NO., a lookup formula with approximate match (that is, match-type set to 1) should be preferred whenever the lookup value is an item number.

2] Lookup values (item numbers) can be selected from a dropdown list constructed by means of data validation.

3] If the lookup table is frequently changing, you can assign a name to the table, which can be defined by means of a dynamic formula.

4] If you include the headers (ITEM NO., ITEM, COST) in the definition of the table, the lookup formula can easily find the column to return from.

Recap:

=VLOOKUP(LookupValue,Table,MATCH("ITEM",INDEX(Table,1,0),0),1)

=VLOOKUP(LookupValue,Table,MATCH("COST",INDEX(Table,1,0),0),1)

would always return the intended results.