How to lookup the values with multiple criterias and yet not filtering the data, cleanest way to do?

[Harshad M]

Dear Ninjas,

I have a situation like below.


I have A & A2 and need to fill the ? in B. ie. lookup marks for each student for each subject in table B. I tried by using filtering B and applying the vlookup, but turned out to be a non-efficient and incorrect way of doing it. Plus if I have to do it on a large scale, what will be the cleanest way to achieve it. So in a nutshell, bring marks by student by subject while not touching other subjects where info in not required.

Thanks.

Harshad M.

 

[Villalobos]

Hi,

Is it an answer for you question?

 

[Harshad M]

Hi,

Is it an answer for you question?

Villalobos thanks, it does work but there is a catch to it.

1. A and A2 are in different files, I am not allowed to stack, append or merge those files. In that case, I will need to reapply the formula in by giving references to individual files(which is OK) and filtering the columns in target file(which perhaps in NOT OK) but would that not add up the conflicts of formulas(I am seeking a cleaner way).

2. As we can see from your solution the SCIENCE subject also gets the formula entry for MARKS, I don't want to touch those cells and populate by any means.

So how can we do this, this is not the question of technical ability but the cleaner way of doing it.

Appreciate and anticipate your response on this.

Thanks.

Harshad M.

 

[Shteven]

Is it OK to have a formula in the Science marks fields if it shows blank?

Here's a solution if you don't want to use an array formula:
Insert a Helper column as Column E with the formula =B9&C9&D9 and drag down.
Then, the formula for your Table B marks field is =IFERROR(VLOOKUP(I9&J9&K9,E:F,2,FALSE),"").

Edit: Sorry, I didn't see your post while I was typing my response. I don't think the above will work for you anymore.

 

[Harshad M]

Is it OK to have a formula in the Science marks fields if it shows blank?

Here's a solution if you don't want to use an array formula:
Insert a Helper column as Column E with the formula =B9&C9&D9 and drag down.
Then, the formula for your Table B marks field is =IFERROR(VLOOKUP(I9&J9&K9,E:F,2,FALSE),"").

Edit: Sorry, I didn't see your post while I was typing my response. I don't think the above will work for you anymore.

Shteven,
Your idea of concatenation is good and is clean, is there any way that vlookup accepts the concatenation as for the array input as the second parameter, i.e. it will look for a concatenation in an array which does not have dummy column but that concatenation happens on the fly and values are fetched.


Thanks.

 

[Shteven]

Shteven,
Your idea of concatenation is good and is clean, is there any way that vlookup accepts the concatenation as for the array input as the second parameter, i.e. it will look for a concatenation in an array which does not have dummy column but that concatenation happens on the fly and values are fetched.


Thanks.

No, I don't believe VLOOKUP can do that (a ninja can correct me if I'm wrong).

I did not look at the solution posted by Villalobos in detail when I typed my reply (I just saw the array brackets and ignored the rest ), but I can now see that he did that exact same thing I did, except with Index Match. If you are looking to have concatenation within your formula instead of in a helper column, use Villa's solution.

 

[bosco_yip]

A normal (non-array) formula way,

K9, formula copy down :

=IFERROR(1/(1/SUMIFS(E:E,B:B,H9,C:C,I9,D:D,J9)),"")

Regards
Bosco

 

[Harshad M]

Folks,
On the same line, I have managed to reach till some point and I think it will solve my problem, but, I am stuck at one point. Any help on this point will be very crucial. How do I delete(clear content)values of cells in a range which contain specific input.? If this is solved then I think I am almost there with the solution, which I can post it here as well once I am done.



So I just want to clear the content (just press the delete button on the keyboard) wherever there is ## (note: ## is actually '## inside--but it shouldn't matter).

PS: These are only 3 columns and few rows, I have several such columns and rows in my worksheet.

Thanks.

 

[Villalobos]

Hi,

Try this macro:

Option Explicit
Sub ReplacePound()

Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row).Select

  Selection.Replace What:="##", Replacement:="", LookAt:=xlPart, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False
  
Range("A1").Select
  
End Sub

 

[Harshad M]

Villalobos,
Thanks for this, I am not very close to Marco but correct me
Replacement:="" will replace the cell value with a SPACE or NULL. I am emphasizing on this because these cells are being used in the calculation of other stuff, so I want null not even a blank because my formula is sensitive to blank i.e. whatever remains in place of ## will override the final value so I don't want blank(it takes it as zero). Null would do a marvelous job.

And if the replacement is null how can apply this Macro to entire sheet i.e increasing range.

Thanks.

 

[Villalobos]

if you would like zero (0) as replacement then use this:

Selection.Replace What:="##", Replacement:="0", LookAt:=xlPart, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False

if you would like to change the range then modify the last column of selected range:

for example: set column D as last column

 Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row).Select

 

[Villalobos]

I assume you will have issue with the numbers which are stored as text

try this:

Option Explicit
Sub ReplacePound()

 Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row).Select
 
  Selection.Replace What:="##", Replacement:="0", LookAt:=xlPart, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False
  Selection.Copy
  Selection.PasteSpecial , xlPasteSpecialOperationAdd
  
  Range("A1").Select
  
  Application.CutCopyMode = False

End Sub