Is it possible to reference a dynamic range in a lookup formula?

=IFERROR(LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)&"","")

The sheet its referencing is EquipmentData. The dynamic range of the C column for this sheet is called EquipmentList.

Should work. You'll need another dynamic range for Column B because the two vectors have to be the same size.
=OFFSET(EquipmentList,0,-1) should work

What type of data is in the range?

Is it text? Numbers? Could be both? Something else?

This will define a dynamic range for TEXT entries:

=$C$3:INDEX($C$3:$C$1048576,MATCH("zzzzz",$C$3:$C$1048576))

This will define a dynamic range for NUMERIC entries:

=$C$3:INDEX($C$3:$C$1048576,MATCH(1E100,$C$3:$C$1048576))

Originally Posted by Nitefox

alvin, can a table be dynamic?

Yes, try:
1. Select range with title row, e.g. A1:C20 where row 1 is your title (ps: this method needs to have a title row or it will create one automatically)
2. Go to [Home] menu, click [Format as Table] and select your preference style
3. The [Format As Table] dialog box will appear, make sure [My table has headers] is checked then click [OK]
4. Once the table created, you shall see the [Design] menu appears. You can modify the table name to something else (if this is your first table, it will be named as "Table1"
5. Assume the title for column B (i.e. cell B1) is "Serialnums", and title for column C (i.e. cell C1) is "EquipmentList" you can use =Table1[Serialnums] to refer to data range in your column B and =Table1[EquipmentList] to refer to data range in your column C
6. As and when you add data to the table, the range will automatically increase

ps: If you want to delete row/column in a defined table, you've to right-click on the cell and choose [Delete] > [Table Rows]/[Table Columns] and not just hit the delete button on your keyboard


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

If it helps I have another dynamic range already called Serialnums for column B.

The two ranges need to be the same size. That's why I suggested using

Formula:

Please Login or Register  to view this content.

If you have already two dynamic ranges do you have problems with simply:
=IFERROR(LOOKUP(2,1/(SerialNums=$G6),EquipmentList)&"","")

Is it possible to sort the data by the SerialNums column?

Originally Posted by Nitefox

Izanol, that formula worked, but it to runs extremely slowly.

Tony Valko, they are text entries. Im not quite sure how to apply that formula to my needs. I also already have dynamic formulas for both the B and C column on the EquipmentData page.

Ok, how did you define the named ranges SerialNums and EquipmentList?

If both ranges contain TEXT entries then define them as follows:

Name: SerialNums
Refers to:

=EquipmentData!$B$3:INDEX(EquipmentData!$B$3:$B$1048576,MATCH("zzzzz",EquipmentData!$B$3:$B$1048576))


Name: EquipmentList
Refers to:

=EquipmentData!$C$3:INDEX(EquipmentData!$C$3:$C$1048576,MATCH("zzzzz",EquipmentData!$C$3:$C$1048576))


Then your formula would be:

=IFERROR(LOOKUP(2,1/(SerialNums=$G6),EquipmentList),"")

OK, thanks for the feedback!