Look up available value to replace missing values

[bncoxuk]

Hi, I have the data below. I want to use available data to replace missing values. For example, the case 2770 appear 3 times, and all of them should have a value of "Low", but it shows #N/A N/A in the first two cases. How can I use formula to achieve this goal?


2770 #N/A N/A

2770 #N/A N/A

3760 High

2770 Low

2730 #N/A N/A

4420 High

2730 #N/A N/A

2730 High

3300 #N/A N/A

3300 #N/A N/A

3300 #N/A N/A

3300 High

 

[bobhc]

Good day bncoxuk


It would help if you said what you where doing to get the correct outcome, (low) and what you are doing to get the wrong outcome (#N/A N/A).


At the moment with what you have supplied it could be any thing!!

 

[bncoxuk]

What I hope to achieve is to find out the values of those missing cases #N/A N/A. For example, in the 4th case, it is "Low" for the case 2770. Therefor, the first two cases (both with 2770) should also be "Low", not #N/A N/A.


This needs some INDEX/MATCH combinations, I believe, to look up those values to replace the #N/A N/A cases.

 

[bobhc]

Good day bncoxuk


#N/A is the error value that means "no value is available." Use #NA to mark empty cells. By entering #N/A in cells where you are missing information, you can avoid the problem of unintentionally including empty cells in your calculations.


How are you getting #N/A in the cells are you putting it there?


If you or some one else is entering #N/A then why not run find and replace to get rid of #N/A and put in "low"

 

[NARAYANK991]

Hi ,


If I have understood you correctly , try this :


=IF(ISNA(B2),IF(ISNA(MATCH("Low"&A2,$B$2:$B$13&$A$2:$A$13,0)),IF(ISNA(MATCH("High"&A2,$B$2:$B$13&$A$2:$A$13,0)),"","High"),"Low"),B2)


Enter this as an array formula , using CTRL SHIFT ENTER , in any unused column , say D2 , and copy down.


I have assumed your data is in the range A2:C13.


The above formula will replace all the #N/A values in column B with other non-error matching values.


Narayan