Excel – Look up on multiple tables – complex

Posted on

QUESTION :

I am trying to make it so that my operators can cut and paste information into an Excel spreadsheet, and then I will have a Vlookup, etc. to be able to get the information I need. The problem is that I have multiple tables that resize based upon information for that day. I cannot hard code the cells for the information.

Also, since there are multiple tables that get copied and I need all of them, the item that I need will be listed several times in column A. I need to be able to have it look up a column based upon the heading and the row based upon the heading. I have looked at indirect, match, etc., but I can’t seem to get it to work.

enter image description here

I’m sorry. The result that I am trying to get is that it will be able to look up the information for example:

The value from the row with ID as the heading and the column with AVIL as the heading. The problem is that data that I am trying to look at might have all 8 rows on one day and then 14 rows the next day or maybe just 5. Not all columns will be available every day which means the column that the result will be in can always be different.

I cannot have separate look ups for each table because that would hard code looking for the information.

ANSWER :

I’ll address the part you make great mention of: being able to find a value at the intersection of a row and column selected by entering their headings. The exact form of your example data makes expanding past that difficult as there are so many possibilities both in how you might receive it and in how you might be able to receive it (given that you seem to have ownership control of things).

So, how to get the data point? You need to turn the headings into something you can select via your lookup formula. That’s doable using the MATCH() function. If your headings are in the first row and the first column, say B1:C1 and row 2:9, you can enter those ranges in two match functions, one for the column headings, one for the row headings: (let’s say you enter the values for the headings in F1 (columns) and F2 (rows)

=MATCH(F1,B1:C1,0)

=MATCH(F2,A2:A9,0)

Minor point here, but it makes a big difference, is, columns for example, the formula will return 1 or 2 but you really need 2 or 3 (because you have the row headings, THEN the two data columns). So start the range one cell to the left (and one cell above for the rows). So you search on “AVIL” and get 2 for the column it heads, and “ID”, getting 5 for the row.

Now you use the INDEX() function to specify the intersection of that column and row and there’s your value:

=INDEX(A1:C9, MATCH(F1,B1:C1,0), MATCH(F2,A2:A9,0))

So, you can easily get the information you need. The only tricky part is that you have to hand enter or mouse in the ranges for the formulas. Might as well just look at the ranges of data and copy and paste the right result, eh? But if you can have those tables of data the paste in go across toward the right edge of the spreadsheet so none of them have anything below their last rows, that problem becomes almost non-existent.

All you do then (assuming the number of columns is consistent, that one table doesn’t have three columns this week, then 11 columns next week), is to use the appropriate range for the columns, which we assuming then never changes, so do it once, done forever, and instead of something like 9 rows (like A1:A9), just use something much higher that will never really be exceeded. A500 perhaps. Sure, 491 blank rows are included in this example, but who cares? That doesn’t affect anything and it makes truly sure you always are looking through ALL of the data, not just a lot of it, AND that you NEVER have to edit the highest row in the ranges.

So you’d then never have to edit the formulas. One pair of cells for each table to enter the lookup info into and a cell to hold the result.

All that could go on a second worksheet so the people pasting data in for you to use never need interact with it.

But… if you don’t require some useful structure to the data entry being done, it really is just a nightmare. And a solution would depend upon exact examples.

Leave a Reply

Your email address will not be published. Required fields are marked *