A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to find a value with multi-column, multi-record list



 
 
Thread Tools Display Modes
  #1  
Old May 30th, 2010, 02:44 PM posted to microsoft.public.excel.worksheet.functions
Dallasm
external usenet poster
 
Posts: 1
Default How to find a value with multi-column, multi-record list

I have a spreadsheet with $costs arranged according to values in both rows
and columns.
How do I create drop down lists for both the rows and columns and then
return the cell value?
eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each
cell within this group how do I create drop downs for each selection, 2-6 and
B-G, and return the value to a selected cell.

Ads
  #2  
Old May 30th, 2010, 05:40 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default How to find a value with multi-column, multi-record list

In a typical table of this nature there would be descriptive row and column
headers. To do a lookup on this table you would then look for the
intersection of a specific row header and a specific column header.

Here's a simplified example...

...........A..........B..........C
1.................gizmoA...gizmoB
2.....blue........12.........10
3.....black......15.........21

You want to know how many black gizmoA's are in stock.

Lookup values:

E1: black
F1: gizmoA

Formula entered in G1:

=VLOOKUP(E1,A1:C3,MATCH(F1,A1:C1,0),0)

The lookup values in E1 and F1 can be selected using a data validation drop
down list. Watch this short video.

How to setup a data validation drop down list:

http://youtube.com/watch?v=t2OsWJijrOM


--
Biff
Microsoft Excel MVP


"Dallasm" wrote in message
...
I have a spreadsheet with $costs arranged according to values in both rows
and columns.
How do I create drop down lists for both the rows and columns and then
return the cell value?
eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each
cell within this group how do I create drop downs for each selection, 2-6
and
B-G, and return the value to a selected cell.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.