How does this VLOOKUP formula work?

[estillbham]

Sorry to ask so basic a question, but I am stumped.


A couple of years ago, I wrote a billing application for my law firm (an assistant and me) in Excel and later moved it to Google Docs (now Drive) with no changes that I can remember in the formulas. I have multiple clients (yea!) who are charged varying amounts per hour. There is a page called Case with the following columns: Staff, CaseName, BillRate. On the Time page, I enter the initials of the staff, the case name, and there is a formula that picks up the rate from the Case page. The formula is

=VLOOKUP(Bx,INDIRECT(Ax),2,0)

[where x changes, depending on the row].


I can't figure out how the formula (which still works) is getting the billing rate from the Case page and putting it on the Time page. Can someone explain my own formula to me?


Ed

 

[Mike10]

lookup_value: this is what you wont to found in firs cell of the tab which have all your data

table_array: this is the tab with your data

col_index_num: number of column from which you would like to get information


=VLOOKUP(lookup_value,table_array,col_index_num)

 

[estillbham]

Thanks, Mike. I understand the VLOOKUP part, but the INDIRECT is confusing me. Shouldn't there be something in the formula that says "go to the Case page"?


Ed

 

[Mike10]

it could be the name of your Tab. for example if you will click on cell A1 it will show you A1 in top left corner, however if you will than click on the A1 text (top left corner) you can change A1 to your own name for example Indirect. All deepens on where are your data it could be same sheet then i dont think it will show you something else apart of Indirect.


Why do you need to know how its works if everything is working fine ? Do you want to create new one?? I think the best is to open excel and try it on your own.

 

[Debraj]

Hi estillbham,


WOW.. Your couple of years old Formula is still working.. Happy to Know..


Let me elaborate your WOW formula..

Suppose you have below data..

[pre]

Staff1	Case 1	80.49%
Staff2	Case 2	17.51%
Staff3	Case 3	25.67%
Staff4	Case 4	82.72%
Staff5	Case 5	82.46%
Staff6	Case 6	95.43%
Staff7	Case 7	36.80%
Staff8	Case 8	19.02%
Staff9	Case 9	67.31%
Staff10	Case 10	30.15%
Staff11	Case 11	75.72%
Staff12	Case 12	89.73%
Staff13	Case 13	81.70%
Staff14	Case 14	86.94%
Staff15	Case 15	41.74%
Staff16	Case 16	55.75%
Staff17	Case 17	2.26%
Staff18	Case 18	75.97%
Staff19	Case 19	8.46%
Staff20	Case 20	35.15%[/pre]
Now enter formula as 


=VLOOKUP(B2,INDIRECT(A2),2,0)

Then select the data Table Area.. i.e A2 to A21.. Go to name Manager > Create From Selection > and Select Left Column..

So Now you have 20 Name, where 'Staff2' is reflect to an array {"Case 2","17.51%"}.. and so on.. You can view this in Name Manager.


When you proceed with Formula..

"B2" = Case 2, and

INDIRECT(A2)=Staff2={"Case 2","17.51%"}

So Vlookup, search in a table {"Case 2","17.51%"}, and match with "Case 2" string, and give you answer as "17.51%" which is 2nd column in your array..


This formula is fully absolute.. so when you drag it down.. B2 changed to B3 and INDIRECT(A2) changes to A3, where A3 is Staff 3 and as per Name Manager Staff 3 is {"Case 3","25.67%"}


Regards,

Deb..

 

[estillbham]

I am trying to create a new billing system for a friend and am copying my own system as the starting point. The sheet with the same formula does not work.


Ed

 

[Debraj]

Select the data Area..Go to Name Manager > Create From Selection > and Select Left Column..

It will still works for you...


Regards,

Deb

 

[Mike10]

than create new one, forget all spreadsheets mate. it will just confuse you, do a new one and go by insert function with step by step...

 

[estillbham]

I figured it out. The formula should have been

=VLOOKUP(B2,Case!$B$1:$C$256,2,0)

Google Docs apparently did not display the "Case!".


Ed

 

[nazmul_muneer]

@estillbham,

Your formula

=VLOOKUP(Bx,INDIRECT(Ax),2,0)

VLOOKUP Formula =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

In the table_array you wrote INDIRECT(Ax). The data of Ax is calling from a name range. Find the values in the name range (Ax).

For searching the value of Ax, Press Ctrl+F3


Regards,


--Muneer