INDIRECT combined with HLOOKUP (2003)

Topic

New Reply

I was having troubles with formulas breaking when I inserted a row into my data sheet. So, with a litte research on this site, I found that what I need to use is the INDIRECT command.

The original formula: =HLOOKUP(‘TC Scenario’!B$3, ‘DATA FEED AT’!$A$1:$BN$400,ROW(),FALSE)
combined with the INDIRECT command…

I created: =INDIRECT(HLOOKUP(‘TC Scenario’!B$3,’DATA FEED AT’!$A$1:$BJ$400,ROW(),FALSE))

Which got me close. When I ran “evaluate formula”, everything was fine until the final combination: INDIRECT(“VALUE “). When it combined further, I got a #REF

Reply | Quote

Replies

The argument to INDIRECT must be a text string that represents a cell range, not a formula. Try this:

=HLOOKUP('TC Scenario'!B$3,INDIRECT("'DATA FEED AT'!$A$1:$BN$400"),ROW(),FALSE)

if you want to “fix” the range in which you do the lookup, or

=HLOOKUP(INDIRECT("'TC Scenario'!B$3"),'DATA FEED AT'!$A$1:$BN$400,ROW(),FALSE)

if you want to “fix” the cell containing the lookup value.

Reply | Quote

Thank you! It worked! (Both worked)

I was looking for a fixed range.

Reply | Quote

I have to recant… it partially worked.

Perhaps I was even asking for the incorrect thing.

What I _want_ is to insert 3 rows between the current rows 3 & 4 (row 4 becomes row 7).
When I do this, I don’t want my formulas to break. I’m _hoping_ for
=HLOOKUP(INDIRECT(“‘TC Scenario’!B$3”),’DATA FEED AT’!$A$1:$BN$400,ROW() +1 ,FALSE)

to become

=HLOOKUP(INDIRECT(“‘TC Scenario’!B$3”),’DATA FEED AT’!$A$1:$BN$400,ROW() -2 ,FALSE)

withouth me fat-fingering each formula.

See attachment New Case screen Row 4

Reply | Quote

Instead of HLOOKUP, you can use INDEX with two MATCH formulas, one for the column lookup and the other for the row lookup. In E4:

=INDEX(‘DATA FEED AT’!$D$3:$F$9,MATCH($D4,’DATA FEED AT’!$B$3:$B$9,0),MATCH(‘TC Scenario’!B$3,’DATA FEED AT’!$D$1:$F$1,0))

This formula can be copied to the other cells.

(The ranges reflect those in your sample workbook, they’d have to be expanded for the real workbook.)

See attached version.

Reply | Quote

The indexing seems to work well!

In the sample provided, I increased the range to include another cell that I am trying to concatenate.

I want to combine rows 20/21 on the data sheet to 1 field on the New Case screen. The code that I currently have in the column is the original HLookup formula.

If it isn’t possible, I could live with updating those formulas manually if rows/columns are shifted, as there are only about 15 total in the whole project…. but it sure would be nice!

Reply | Quote

You could use this in E21:

=INDEX(‘DATA FEED AT’!$D$3:$F$25,MATCH(“Age”,’DATA FEED AT’!$B$3:$B$25,0),MATCH(‘TC Scenario’!B$3,’DATA FEED AT’!$D$1:$H$1,0))&” “&INDEX(‘DATA FEED AT’!$D$3:$F$25,MATCH(“Unit”,’DATA FEED AT’!$B$3:$B$25,0),MATCH(‘TC Scenario’!B$3,’DATA FEED AT’!$D$1:$H$1,0))

It uses literal search strings “Age” and “Unit” instead of cell references.

Reply | Quote