Vlookup with a changing table array

take a look at using index/match/match for this

the syntax would be...
=index(range to look in,row to look in,column to look in)
which becomes...
=index(range to look in, match(value to find, range to find it in, 0), match(value to find, column to find it in,o) the ,0 is to find an exact match

it is hard for me to try and convert your vlookup to the index/match, but if you have a problem implementing this, perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

index/match version...
=INDEX($A$7:$B$20,MATCH("Debt",$A$7:$A$20,0),2)
vlookup version...
=VLOOKUP("debt",$A$7:$B$20,2,FALSE)

I had tried these formula's previously, however they do not work for the following reason:

As I am taking the data from a 3rd party for company A, company B etc; for company A, the cell A7 might = "Total Liabilities", however for company B it might be A16 = "Total Libailities, and the same for "Current Liabilities" (company A = A1, Company B = A12). This would result in the following scenario's:

Company A Table Array = A1:A7
Company B Table Array = A12:A16
Company B Table Array...

I do not know how to define a Table Array using a common reference of "Total Liabilities" and "Current Liabilities" to find a variable line number in Column A. Otherwise, I will need to manually assign the Table array range every time I Import the data.

Thanks,

Jon

could you show an example of where the above formulas do not work?
in the meantime, maybe this is what you are looking for :

Formula:

Please Login or Register  to view this content.

Hope this helps

does it really matter what the range is, if you are looking to find the value of "Debt" (or any other criteria for that matter), just make the search range big enough to accomodate the longest list?

=VLOOKUP("debt",$A$1:$B$1000,2,FALSE)
=INDEX($A$1:$B$1000,MATCH("Debt",$A$1:$A$1000,0),2)

you are welcome


Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

@ FDibbins
I think OP may be concerned that the lookup would pick out the criteria from elsewhere in the list, restricting the range to between the two other criteria would minimize that possibility..

your formulas are definitely the better (and faster) approach, as long as the array only has the "debt" criteria once..

you may be right, dred, thannks