HLookup with and function

[rajasekhargvd]

Hi Can anybody help me to create a formula: as per the attached file,
1. cell R6 will be a drop down menu of a, b & c
2. whenever a b or c is selected in R6 the corresponding values of 1, 2 3 should be appeared in S6, T6 & U6 respectively
3. Also suggest incase of d, e, f ...... are joining in the data.

Thanks in advance.

Raj

[azumi]

Hi there, hope this works


cheers

[oeldere]

you use merged cells in your file , don't use them, you get in trouble with it sooner or later.

your example don't mach your question.

your have 4 columns of data. In your text you refer to 3 columns.

column 4 is not needed?

P.s. I don't see the dropdown in cell S6

[rajasekhargvd]

Noted about the Merging Cells.

Secondly Yes, my thought was if 4th column is required can copy and paste the formula from left.........

[rajasekhargvd]

Dear Azumi,

Thanks for your reply, sorry if am not clear my question, I want to establish this formula row wise. I mean, I want to select 'a' in 6th row, 'b' in 7th row and like...

Can you please revise your formula.

[oeldere]

s6=

Please Login or Register  to view this content.

See the attached file.

[rajasekhargvd]

Thanks for a very simple solution, though I was not expecting to have a1, a2......

Thanks again.

[Punnam]

try this

with small changes

Punnam

[oeldere]

@rajasekhargvd

Please Login or Register  to view this content.

You get several replies, so it is usefull to the (other) forummembers, to add to whom you are replying.

If you comment to me (oeldere), please explain your comment below, since I don't understand that comment.

Please Login or Register  to view this content.

[rajasekhargvd]

try this

with small changes

Punnam

As I wanted to keep sanitized version I have renamed to 'a', 'b', 'c', '1', '2', '3', '4'......... but these are names of different categories and series, hence don't think the 'Sumif' function will be an idle solution. can you help me without bothering about the headings....

[rajasekhargvd]

@rajasekhargvd

Please Login or Register  to view this content.

You get several replies, so it is usefull to the (other) forummembers, to add to whom you are replying.

If you comment to me (oeldere), please explain your comment below, since I don't understand that comment.

Please Login or Register  to view this content.

I mean, I don't want use like 'a1', 'a2' etc., as the 'a', 'b', '1', '2' etc., are just used in the place of names of categories and series. in this scenario I might need to have a dummy row with 'concatente' of those.

[Punnam]

As I wanted to keep sanitized version I have renamed to 'a', 'b', 'c', '1', '2', '3', '4'......... but these are names of different categories and series, hence don't think the 'Sumif' function will be an idle solution. can you help me without bothering about the headings....

In that case you should clarify the below
1) Was "A" name is common for all the three sub division 1 2 3 4
2) 1,2,3,4 are they same for a,b,c
3) IF not same weather it is possible for us match them
4) What is the logic between A & 1,2,3&4
Punnam

[oeldere]

I mean, I don't want use like 'a1', 'a2' etc., as the 'a', 'b', '1', '2' etc., are just used in the place of names of categories and series. in this scenario I might need to have a dummy row with 'concatente' of those.

OK, but what is the problem with that?

Post 10
As I wanted to keep sanitized version I have renamed to 'a', 'b', 'c', '1', '2', '3', '4'......... but these are names of different categories and series, hence don't think the 'Sumif' function will be an idle solution. can you help me without bothering about the headings....

And did you try the solution of Punnam with the names changed?
I believe they should work.

Pleasse reply on both questions.

[rajasekhargvd]

In that case you should clarify the below
1) Was "A" name is common for all the three sub division 1 2 3 4
2) 1,2,3,4 are they same for a,b,c
3) IF not same weather it is possible for us match them
4) What is the logic between A & 1,2,3&4
Punnam

Please find here below the clarifications:
1 - Yes, 'a' is having 4 sub-divisions called 1,2,3,4
2 - Yes, 1,2,3,4 are same for a,b,c.....
3 - Those are same
4 - No specific logic only 1, 2, 3, 4 are unique sub divisions of a, b, c...

Hope this clarifies your queries.

[rajasekhargvd]

I mean, I don't want use like 'a1', 'a2' etc., as the 'a', 'b', '1', '2' etc., are just used in the place of names of categories and series. in this scenario I might need to have a dummy row with 'concatente' of those.

OK, but what is the problem with that?

Post 10
As I wanted to keep sanitized version I have renamed to 'a', 'b', 'c', '1', '2', '3', '4'......... but these are names of different categories and series, hence don't think the 'Sumif' function will be an idle solution. can you help me without bothering about the headings....

And did you try the solution of Punnam with the names changed?
I believe they should work.

Pleasse reply on both questions.

1 - As I already mentioned the only issue would be having a dummy row
2 - I tried and it is not working when I replace the 1, 2, 3, 4 with names (text)

[Punnam]

If that the case y cant u use my solution by just change the value of A,B,C in the
Un merging ? was it compulsory to be merge .
Punnam

[oeldere]

1 - As I already mentioned the only issue would be having a dummy row

And that is an issue to you (having an extra row)?

You could hide that row if you like.


2 - I tried and it is not working when I replace the 1, 2, 3, 4 with names (text)

and the names in the rows AND in the drop down cells are 100% equal?

Check that first, and if so (whatch also leading or trailing spaces), post the file with an example.

first make a dropdownlist as you noticed in your file.

[Punnam]

Hi,

See this can be done in many approach's but what we like to provide a simple one, as the same will be easy to work and modify
U can try this if work fine it need to follow few instructions

Punnam

[rajasekhargvd]

Hi,

See this can be done in many approach's but what we like to provide a simple one, as the same will be easy to work and modify
U can try this if work fine it need to follow few instructions

Punnam

Thanks Punnam, it worked well - I made a small change in the formula '$N$6' changed to '$N6' to make sure that the next row will not dependent on the row 6.
Thanks a lot - I didn't expected that this can be done without 'HLookup'.!!!

[Punnam]

HI Rajasekhar,

u r welcome . if u question answered/Solved mark the thread solved and u can tank one who helped you by adding some reputation points.
This can be done by Star Button "Add Reputation" .on left corner of any of my posts .

Punnam