Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 19th 12, 08:34 PM
Member
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 89
Default LOOKUP instead of Index and Match

I have an Excel spreadsheet with inventory numbers on it from various distribution centers. They come with their names, dates, commodity amounts, etc each day. What I want to do is set up a formula that will find the center's number in Column A and return the commodity amount from Column C. Also, if the center's number isn't found, then I would like for it to return "0", or N/A, or something like that to show the number wasn't found.

I've been trying to use:

LOOKUP(E1+100,CHOOSE({1,2},0,INDEX($K$2:$K$24,MATC H(1,INDEX(($H$2:$H$24=$H$12)*($K$2:$K$24=$K$12),), ))))

But, I can't seem to configure it to my needs.

Any/all assistance is greatly appreciated.

Thank you

  #2   Report Post  
Old March 16th 12, 10:48 PM
Senior Member
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 663
Default

Quote:
Originally Posted by frankjh19701 View Post
I have an Excel spreadsheet with inventory numbers on it from various distribution centers. They come with their names, dates, commodity amounts, etc each day. What I want to do is set up a formula that will find the center's number in Column A and return the commodity amount from Column C. Also, if the center's number isn't found, then I would like for it to return "0", or N/A, or something like that to show the number wasn't found.

I've been trying to use:

LOOKUP(E1+100,CHOOSE({1,2},0,INDEX($K$2:$K$24,MATC H(1,INDEX(($H$2:$H$24=$H$12)*($K$2:$K$24=$K$12),), ))))

But, I can't seem to configure it to my needs.

Any/all assistance is greatly appreciated.

Thank you
Hi, any chance you could show us an example of the workbook to make it easier to understand the issue?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
lookup H&V...or match...index??? Kevin W[_2_] Excel Discussion (Misc queries) 6 April 1st 10 02:42 PM
Lookup, index, match? Kikuchisawa Excel Worksheet Functions 1 March 10th 08 10:14 PM
LOOKUP, MATCH, INDEX? bob Excel Worksheet Functions 13 June 6th 07 04:26 AM
Lookup/match/index Jules Excel Discussion (Misc queries) 1 July 5th 06 05:02 PM
Lookup/Index/Match HELP! Ellen G. Excel Discussion (Misc queries) 1 February 13th 06 04:15 PM


All times are GMT +1. The time now is 01:40 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017