Searching for simplicity...

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
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
ABCDEFGHIJ
1Iteration1ITEMNOITEMCOSTITEMNOITEMCOST
22Orange$0.551Apple$0.50
32Orange$0.55
43Banana$0.75
5
6
7
8Iteration2ITEMNOITEMCOSTITEMNOITEMCOST
92Orange$0.551Apple$0.50
102Orange$0.55
113Banana$0.75
12
13
14
15Iteration3ITEMNOITEMCOSTITEMNOCOLORITEMCOST
162Orange$0.551RedApple$0.50
172OrangeOrange$0.55
183YellowBanana$0.75
Sheet1
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

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
Top