2 dimensional lookup

**vamshi57** · 01-09-2011, 05:58 AM

In the spreadsheet attached I have two worksheets A and B . A has the raw data

(a) - Based on the raw data . Values should be in returned in C3 of B worksheet using the lookup formulas . It should look for C2 from Column A of A worksheet & Match it with B3 from Row A of A worksheet .

Incase if there are no values , it should return zeros .

(b) In worksheet B , Cell C2 has the data validation list , each time I update the sheet A I need to manually change the range . Can I get a formula , so that the range is automatically adjusted based on the data in worksheet A .

Thank you

**DonkeyOte** · 01-09-2011, 06:03 AM

Given all of the constraints mentioned and given numeric data types:

a)

Please Login or Register  to view this content.

b)

Change RefersTo to:

Please Login or Register  to view this content.

**vamshi57** · 01-09-2011, 06:20 AM

(A) - Its giving me some error while copying the formula and the error is pointing to the choose function .

(B) - I used this formula in the data validation field - But it says "You may not use references from other worksheets " . Does this formula works with the assumption that the data can go upto 255 values ONLY ?

**DonkeyOte** · 01-09-2011, 06:30 AM

a) typo - simply missing opening parenthesis before {1,2}

Please Login or Register  to view this content.

b) I was not referring to DV but to the Named Range definition: RefersTo (not Source)

**vamshi57** · 01-09-2011, 06:43 AM

Excellent Thank you so much . As always, you are one of the best !!

Sir , I have posted a question with subject line "Multiple workbooks consolidation with validation" , three days back and NO one responded to it . I'm sure you can help me with that . Can you please look into it . Thanks in advance !

**vamshi57** · 01-09-2011, 06:54 AM

You know what I just realized that the look up formula is not returning correct values .

For ex - Choose "ispna_firewall" in cell C2 , You'll notice value 7 returned for " Rejected " , but infact there is no heading with that in the raw data , it should ideally be returning Zero . Also its returning value 2 for "Cancelled due to emergency " , but the raw data shows it as zero . Can you look into this formula again . Thank you

**DonkeyOte** · 01-09-2011, 06:57 AM

missing 0 in final MATCH (match_type) - again the result of typo on my part - apologies for confusion.

**vamshi57** · 01-09-2011, 07:05 AM

Now it worked thank you .

No one is helping me with my other post in this forum with subject line "Multiple workbooks consolidation with validation" . Can you please help me with that .

**DonkeyOte** · 01-09-2011, 07:11 AM

Yes, I believe you mentioned that already.

People will help where and when they can.

A lack of response generally results from lack of clarity.
If you feel you need to "bump"/clarify your post do so by replying to it appropriately
(note bumping "guideline" in general: >= 12 hours without response)