Formula to display the value of a cell based on the contents of another cell

RoyR

Board Regular
Joined
Jan 25, 2012
Messages
62
I hope I can explain this clearly. I have a workbook that contains indepth information about novels. There is one sheet that contains an array of data about the novels and is named 'Data'. There is another sheet which contains Statistics about all the novels in the workbook such as the books genre, Action, Adventure, Romance etc., and how many of each.

So lets say on the statistics sheet, Cell A1 is 'Action' and B1 is the number of books with this genre. A2 is 'Adventure' and B2 contains the number of books. And so forth. Now, another sheet is deticated to a spicific book and contains all the information about that book only. What I want to do is as follows. If A15 of the deticated book sheet lists 'Action' as the genre, then I want cell B15 to give the value of how many other books in the library have the same genre. So B15 of the deticated sheet would match A15 with the same value under columb A of the statistics sheet and return the value in columb B of the statistics sheet.

I hope this is clear. I know how to do it with a macro, but formulas are a weak point for me.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
it is not clear if you like you can send me your file to my email i can do it for you
 
Upvote 0
The file contains the personal information of many people so I can not send it. Let's see if I can explain better with an example

Sheet1
Cell A1 = Action Cell B1 = 234
Cell A2 = Adventure Cell B2 = 157
Cell A3 = Romance Cell B3 = 96
and so forth

Sheet2
If cell A1 = Adventure then B1 should = 157

So the formula I am looking for will compare Cell A1 with sheet1 cells, A1, A2, A3.... until it finds a match and then return the B cell value.

I have been playing with the formulas form hours now hoping to find right one but having no luck.

Thank you for your time.
 
Upvote 0
At sheet 2
at a1=Adventure
at B1 =VLOOKUP(A1,Sheet1!A1:B27,2,0)

B27 27 is the last row of sheet 1 table data number
 
Upvote 0
Sounds like you need a lookup formula.

Using your example below, in cell B1 of Sheet 2 write =VLOOKUP(A1,Sheet1!$A$1:$B$10,2,FALSE)

The range A1:B10 should be replaced by the relevant range of your list in sheet 1.

Hope this helps.
 
Upvote 0
it is not clear if you like you can send me your file to my email i can do it for you

That is not how the forum works - all questions should be addressed first in the open boards, though we do sometimes allow it where all other avenues have been exhausted and if the information is too personal to post on a file hosting site.
 
Upvote 0
B1 =VLOOKUP(A1,Sheet1!A1:B27,2,0) works perfectly. One quick question. If the referenced data was in another open workbook, how would the formula be written then?

I have become fairly good at writing macros, but formulas seem to escape me.

Thanks to everyone who has offered help.
 
Upvote 0
select same equation
at Sheet1!A1:B27
remove it
it will be like this

=VLOOKUP(A1,,2,0)
between the two commas select the table you want to study starting its first cell up to last row 2nd colum
 
Upvote 0

Forum statistics

Threads
1,203,756
Messages
6,057,162
Members
444,909
Latest member
Shambles111

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top