How to find cell reference from table_array

**sisinsure** · 01-09-2012, 01:22 PM

Hi, I have a spreadsheet with formulas in cells already. I'm trying to add, look up, and modify some fields but I don't know how to find a tabble array. It's my first time researching this and it looks beyond my scope of knowledge so I was wondering if anyone knew how to identify or locate where a cell is looking up in relation to the table_array that is there. It looks like this:

Equation:
=VLOOKUP(A9,$O$7:$R$531, 4 ,FALSE)

I'm trying to look up what's here and modify the fields in this table_array:
A9,$O$7:$R$531

tablearray.jpg

Additional info: when i input the class code i.e. 5240 it populates a description on the cell next to it.

I would be very grateful for any help.

Thanks

**NBVC** · 01-09-2012, 01:33 PM

Don't understand the question. Are you saying that the VLOOKUP is not giving the correct results?

**sisinsure** · 01-09-2012, 01:38 PM

Sorry about that, I'm trying to "reverse engineer" this spreadsheet so to say. I'm just starting to learn excel so I'm not to sure how to find what these values represent and where they are located on the spreadsheet.

So when I see A9,$O$7:$R$531 I don't know necessarily where it is on the spreadsheet or where it's pulling the data from. I want to be able to recreate this, but I need to know how this (A9,$O$7:$R$531) pulls it's data, Is there a way for me to find this out?

I hope I'm being clear enough. Thanks for responding.

**NBVC** · 01-09-2012, 01:41 PM

Have a look at the VLOOKUP function in Excel's help files... it explains the syntax with examples.

**sisinsure** · 01-09-2012, 01:56 PM

Correct me if I'm wrong, if I'm reading this correctly, if you look up the picture I attached it might help... =VLOOKUP(A9,$O$7:$R$531,4,FALSE) means when I enter a value (the class code #) into cell A9 then it looksup cell 07 through R531 to find the matching number, then I get lost from here hehe, I'm not sure what the 4, false means. But am I on the right track?

**NBVC** · 01-09-2012, 02:02 PM

Did you look at the help files?

Here is another link: http://www.contextures.com/xlfunctions02.html#Arguments

Syntax:

=VLOOKUP(lookup_value, lookup_table, column_index_number,[range_lookup])

so A9 is your lookup_value.

It looks for that value in the first column of the lookup_table, $O$7:$R$531 and returns the item that is in the 4th column of that table, corresponding to the row within the table that the match is found. The FALSE means to look for an exact match, rather than an approximate match

**sisinsure** · 01-09-2012, 03:15 PM

Thank you NBVC, you've made it clear to me now. I have a seperate question now, that's on a whole different field. In my screenshot you can't see the columns O, P, Q, or R that hold these values. If the columns are hidden is there a way to reveal them?
tabblearray2.jpg

**NBVC** · 01-09-2012, 03:47 PM

Click and hold the column L header and drag mouse over to the V column header so that both are selected. Then right-click the mouse and select Unhide from the pop-up menu.